Enter 2001 April/EnterCD4.iso/Update/SQL Server SP3/sql70sp3i.exe/INSTALL/sqldmo.sql

Text File | 2000-10-24 | 157.9 KB | 3,843 lines

/**********************************************************************
 * Microsoft SQL Server SQL-DMO scripts, version 7.0.
 * Date created:  3/17/96.
 * Date modified: 2/26/97.
 * Copyright Microsoft Corporation, 1992-1997
 **********************************************************************
 *    This script must be installed for the SQL-DMO (SQLDMO) objects and
 *    the SQL Enterprise Manager to run against a Microsoft SQL Server.
 **********************************************************************/
















/* Preprocessor directives, will be blank space in output .sql file. */






/* For fetching from cursor */

















































/* status values for these. */







/* bitmask values for same; power(2, DRI_*). */


















/* DRI-generated index masks, to apply to sysindexes.status */






/* sysobjects.category bit that indicates this is an MS-internal object. */


/* sysobjects.category bit for an sp_ that indicates it's a startup proc, or an xp that should ImpersonateClient. */



/* BIT_CLUSTERED indicates the key is clustered. */
/* EXCLUDE REPLICATION value in sysconstraints.status, and system-generated name. */





/* sysobjects.sysstat bits (lower 4) that mask off the object type. */


/* bit for DEFAULTS which are really DRI-created. */


/* bits for sp_MShelpcolumns col_flags - don't conflict with bit_sysgenname for DRIDefaults. */





/* sysdatabases.category bits */



/* sysobjects.category bits (from ntdbms\object.h) */

















/* The parser can't '|' 0x-prefixed types as it thinks they're binary */







/**** daVinci additions ****/

/* sp_MStablekeys @flags bitmask param values. */


/* sp_MShelpindexes @flags bitmask param values. */


/* sp_MStablechecks @flags bitmask param values. */






/* Internal #defines for SQLDMO. */

/* Current SQLDMO version.  Also must be available to SQLDMO.odl. */



/* Make sure we use our own; shield SQLDMO from Starfighter changes. */







/* Our max name lengths. */
/* !! All lengths retrieved from server must be rounded UP TO THE NEXT 4-bytes. !! */
/* There must be at least one byte more than server max length, for the NULL byte. */

/* 7.0 Identifier length has been increased from 30 to 128 characters */

















/* Keep for Replication, but changed to SYSNAME length */
































/* 7.0 */



/* Reserve enough space for [] quoting character, and escape character in identifier */




















/* Specail Identifier length for mapping table when scripting from 6.x server, this is the sysname length for 6.x server */





use master
go

print ''
print 'Creating SQLDMO stored procedures...'
print ''

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print ''
print 'Dumping transaction log...'
print ''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/********************** Include individual components definitions *********************************/




















































/* From perm.h */














/* Roles */


















/* File Growth */


/* Max. Column length */



/********************* Delete existing objects *********************************/
print N''
print N'Deleting existing objects...'
print N''
go

if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpcolumns')
    drop procedure sp_MShelpcolumns
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpindex')
    drop procedure sp_MShelpindex
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelptype')
    drop procedure sp_MShelptype
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdependencies')
    drop procedure sp_MSdependencies
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablespace')
    drop procedure sp_MStablespace
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSindexspace')
    drop procedure sp_MSindexspace
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSuniquename')
    drop procedure sp_MSuniquename
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSkilldb')
    drop procedure sp_MSkilldb
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSobjectprivs')
    drop procedure sp_MSobjectprivs
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSloginmappings')
    drop procedure sp_MSloginmappings
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablekeys')
    drop procedure sp_MStablekeys
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablechecks')
    drop procedure sp_MStablechecks
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablerefs')
    drop procedure sp_MStablerefs
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSsettopology')
    drop procedure sp_MSsettopology
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSmatchkey')
    drop procedure sp_MSmatchkey
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachdb')
    drop procedure sp_MSforeachdb
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachtable')
    drop procedure sp_MSforeachtable
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeach_worker')
    drop procedure sp_MSforeach_worker
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSSQLOLE_version')
    drop procedure sp_MSSQLOLE_version
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSSQLOLE65_version')
    drop procedure sp_MSSQLOLE65_version
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSSQLDMO70_version')
    drop procedure sp_MSSQLDMO70_version
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSscriptdatabase')
    drop procedure sp_MSscriptdatabase
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSscriptdb_worker')
    drop procedure sp_MSscriptdb_worker
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuseraccess')
    drop procedure sp_MSdbuseraccess
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuserpriv')
    drop procedure sp_MSdbuserpriv
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpfulltextindex')
    drop procedure sp_MShelpfulltextindex
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpfulltextscript')
    drop procedure sp_MShelpfulltextscript
go
/* sp_MSqv have been removed, but we want to keep this query, just in case there are left over from previous build */
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSqv')
    drop procedure sp_MSqv
go


/********************* Create new objects *********************************/

/* New validation added for 7.0 */
/* if (@@microsoftversion >= SQL70_MINVERSION) begin                                       */
/*     exec sp_MS_upd_sysobj_category 1                                                       */
/* end else begin                                                                          */
/*     RAISERROR 55555 'You need a released version of SQL 7.0 to run this SQLDMO script'     */
/* end                                                                                     */
if (@@microsoftversion < 0x07000000) begin
    RAISERROR 55555 N'You need a released version of SQL 7.0 to run this SQLDMO script'
end

/*******************************************************************************/
print N''
print N'Creating sp_MShelpcolumns'
print N''
go
create procedure sp_MShelpcolumns
@tablename nvarchar(517), @flags int = 0, @orderby nvarchar(10) = null, @flags2 int = 0
as

   /* For non-string columns, sp_MShelpcolumns returns the length in syscolumns.length, */
   /* which is defined in BOL as "maximum physical storage length from systypes".       */
   /* For string columns (including types based on string types), sp_MShelpcolumns      */
   /* returns this maximum length in characters (i.e. it returns syscolumns.length      */
   /* adjusted to whether the column is based on char or nchar).                        */

   /*** @flags2 added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MShelpcolumns '%s', null, 'id', 1                           ***/

/** For DaVinci **/
/** Use sp_help filtering of precision/scale (only fordecimal/numeric types; else use NULL). **/


    if @flags is null
        select @flags = 0
    if (@tablename = N'?')
    begin
        print N''
        print N'Usage:  sp_MShelpcolumns @tablename, @flags int = 0'
        print N' where @flags is a bitmask of:'
        print N' 0x0200        = No DRI (ignore Checks, Primary/Foreign/Unique Keys, etc.)'
        print N' 0x0400        = UDDTs --> Base type'
        print N' 0x80000        = TimestampToBinary (convert timestamp cols to binary(8))'
        print N' 0x40000000    = No Identity attribute'
        return 0
    end

    declare @objid int
    select @objid = object_id(@tablename)
    if (@objid is null)
    begin
        RAISERROR (15001, -1, -1, @tablename)
        return 1
    end

    set nocount on

    create table #sphelpcols
    (
       col_name            nvarchar(128)            NOT NULL,
       col_id            int                    NOT NULL,
       col_typename        nvarchar(128)            NOT NULL,
       col_len            int                    NOT NULL,
       col_prec            int                    NULL,
       col_scale        int                    NULL,
       col_numtype        smallint            NOT NULL,    /* For DaVinci to get sp_help-type filtering of prec/scale */
       col_null            bit                    NOT NULL,    /* status & 8 */
       col_identity        bit                    NOT NULL,    /* status & 128 */
       col_defname        nvarchar(257)            NULL,        /* fully-qual'd default name, or NULL */
       col_rulname        nvarchar(257)            NULL,        /* fully-qual'd rule name, or NULL */
       col_basetypename    nvarchar(128)            NOT NULL,
       col_flags        int                    NULL,        /* COL_* bits */
       col_seed            numeric (28)      NULL,
       col_increment    int                 NULL,
       col_dridefname    nvarchar(128)            NULL,        /* DRI DEFAULT name */
       col_dridefid    int                    NULL,         /* remember the DRI DEFAULT id in syscomments, so we can retrieve it later */
      col_iscomputed int               NOT NULL,
       col_objectid    int                    NOT NULL,     /* column object id, need it to get computed text from syscomments */
       col_NotForRepl    bit                    NOT NULL,     /* Not For Replication setting */
       col_fulltext    bit                    NOT NULL,     /* FullTextIndex setting */
       col_AnsiPad     bit               NULL,         /* Ansi_Padding setting */
      /* following columns are repeating the info from col_defname and col_rulname                  */
      /* because we can not change data in col_defname and col_rulname, since daVinci is using them */
       col_DOwner     nvarchar(128)            NULL,        /* non-DRI DEFAULT owner, or NULL */
       col_DName      nvarchar(128)            NULL,        /* non-DRI DEFAULT name, or NULL */
       col_ROwner     nvarchar(128)            NULL,        /* non-DRI RULE owner, or NULL */
       col_RName      nvarchar(128)            NULL,        /* non-DRI RULE name, or NULL */
    )

   /* Do not store the computed text in this temp table, because one extra join causes big performance hit */
    /* First load stuff so we can blot off inappropriate info and massage as per @flags */
    insert #sphelpcols
        select c.name, c.colid, st.name,
         case when bt.name in (N'nchar', N'nvarchar') then c.length/2 else c.length end,
            ColumnProperty(@objid, c.name, N'Precision'),
            ColumnProperty(@objid, c.name, N'Scale'),
                -- col_numtype for DaVinci:  use sp_help-type prec/scale filtering for @flags2 & 1
            case when (@flags2 & 1 <> 0 and bt.name in (N'tinyint',N'smallint',N'decimal',N'int',N'real',N'money',N'float',N'numeric',N'smallmoney'))
                    then 1 else 0 end,
                -- Nullable
            convert(bit, ColumnProperty(@objid, c.name, N'AllowsNull')),
                -- Identity
            case when (@flags & 0x40000000 = 0) then convert(bit, ColumnProperty(@objid, c.name, N'IsIdentity')) else 0 end,
                -- Non-DRI Default (make sure it's not a DRI constraint).
            case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else user_name(d.uid) + N'.' + d.name end,
                -- Non-DRI Rule
            case when (c.domain = 0) then null else user_name(r.uid) + N'.' + r.name end,
                -- Physical base datatype
            bt.name,
                -- Initialize flags to whether it's a length-specifiable type, or a numeric type, or 0.
            case when st.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001
                    when st.name in (N'decimal',N'numeric') then 0x0002
                    else 0 end
                    -- Will be NULL if column is not UniqueIdentifier.
                    + case isnull(ColumnProperty(@objid, c.name, N'IsRowGuidCol'), 0) when 0 then 0 else 0x0008 end,
                -- Identity seed and increment
            case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_seed(@tablename) else null end,
            case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_incr(@tablename) else null end,
                -- DRI Default name
            case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
                    then object_name(c.cdefault) else null end,
                -- DRI Default text, if it does not span multiple rows (if it does, SQLDMO will go get them all).
            case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
                    then t.id else null end,
         c.iscomputed,
         c.id,
                -- Not For Replication
            convert(bit, ColumnProperty(@objid, c.name, N'IsIdNotForRepl')),
         convert(bit, ColumnProperty(@objid, c.name, N'IsFulltextIndexed')),
         convert(bit, ColumnProperty(@objid, c.name, N'UsesAnsiTrim')),
                -- Non-DRI Default owner and name
            case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else user_name(d.uid) end,
            case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else d.name end,
                -- Non-DRI Rule owner and name
            case when (c.domain = 0) then null else user_name(r.uid) end,
            case when (c.domain = 0) then null else r.name end
        from syscolumns c
                -- NonDRI Default and Rule filters
            left outer join sysobjects d on d.id = c.cdefault
            left outer join sysobjects r on r.id = c.domain
                -- Fully derived data type name
            join systypes st on st.xusertype = c.xusertype
                -- Physical base data type name
            join systypes bt on bt.xusertype = c.xtype
                -- DRIDefault text, if it's only one row.
            left outer join syscomments t on t.id = c.cdefault and t.colid = 1
                    and not exists (select * from syscomments where id = c.cdefault and colid = 2)
        where c.id = @objid
        order by c.colid

    /* Convert any timestamp column to binary(8) if they asked. */
    if (@flags & 0x80000 != 0)
        update #sphelpcols set col_typename = N'binary', col_len = 8, col_flags = col_flags | 0x0001 where col_typename = N'timestamp'

    /* Now see what our flags are, if anything. */
    if (@flags is not null and @flags != 0)
    begin
        if (@flags & 0x0400 != 0)
        begin
            /* Track from xusertype --> b.<base>xtype --> u.xusertype in systypes */
            /* First mask off the things we will set.  The convert() awkwardness is */
            /* necessitated by SQLServer's handling of 0x-prefixed values. */
            declare @typeflagmask int select @typeflagmask = (convert(int, 0x0001) + convert(int, 0x0002))
            update #sphelpcols set col_typename = b.name,
                -- ReInitialize flags to whether it's a length-specifiable type, or a numeric type, or 0.
                col_flags = col_flags & ~@typeflagmask
                            + case when b.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001
                                when b.name in (N'decimal',N'numeric') then 0x0002
                                else 0 end
            from #sphelpcols c, systypes n, systypes b
                where n.name = col_typename                --// xtype (base type) of name
                    and b.xusertype = n.xtype            --// Map it back to where it's xusertype, to get the name
        end
    end

    /* Determine if the column is in the primary key */
    if (@flags & 0x0200 = 0 and (OBJECTPROPERTY(@objid, N'TableHasPrimaryKey') <> 0)) begin
        declare @indid int
        select @indid = indid from sysindexes i where i.id = @objid and i.status & 0x0800 <> 0
        if (@indid is not null)
            update #sphelpcols set col_flags = col_flags | 0x0004
            from #sphelpcols c, sysindexkeys i
                where i.id = @objid and i.indid = @indid and i.colid = c.col_id
    end

    /* OK, now put out the data.  @flags2 added for DaVinci; currently only bit 1 (sp_help filtering of prec/scale) is relevant. */
    set nocount off
    if (@orderby is null or @orderby = N'id')
    begin
        select c.col_name, c.col_id, c.col_typename, c.col_len,
                    -- Prec/scale only for numeric/decimal
                col_prec = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
                        then c.col_prec else NULL end,
                col_scale = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
                        then c.col_scale else NULL end,
                col_basetypename, c.col_defname, c.col_rulname, c.col_null, c.col_identity, c.col_flags,
               c.col_seed,
            c.col_increment, c.col_dridefname, cn.text, c.col_iscomputed, cm.text, c.col_NotForRepl,
            c.col_fulltext, c.col_AnsiPad, c.col_DOwner, c.col_DName, c.col_ROwner, c.col_RName
        from (#sphelpcols c
      left outer join syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid
        order by c.col_id
    end else begin
        select c.col_name, c.col_id, c.col_typename, c.col_len,
                    -- Prec/scale only for numeric/decimal
                col_prec = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
                        then c.col_prec else NULL end,
                col_scale = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
                        then c.col_scale else NULL end,
                col_basetypename, c.col_defname, c.col_rulname, c.col_null, c.col_identity, c.col_flags,
               c.col_seed,
            c.col_increment, c.col_dridefname, cn.text, c.col_iscomputed, cm.text, c.col_NotForRepl,
            c.col_fulltext, c.col_AnsiPad, c.col_DOwner, c.col_DName, c.col_ROwner, c.col_RName
        from (#sphelpcols c
      left outer join syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid
        order by c.col_name
    end

go
/* End sp_MShelpcolumns */

/*******************************************************************************/
print N''
print N'Creating sp_MShelpindex'
print N''
go
create procedure sp_MShelpindex
@tablename nvarchar(517), @indexname nvarchar(258) = null, @flags int = null
as
   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MShelpindex '%s', null, 1                                  ***/

   /* @flags is for daVinci */
   if (@flags is null)
      select @flags = 0
   if (@flags <> 0)
   begin
   /* daVinci is calling */
      select i.name, i.status, i.indid, i.OrigFillFactor,
      IndCol1 = index_col(@tablename, i.indid, 1),
      IndCol2 = index_col(@tablename, i.indid, 2),
      IndCol3 = index_col(@tablename, i.indid, 3),
      IndCol4 = index_col(@tablename, i.indid, 4),
      IndCol5 = index_col(@tablename, i.indid, 5),
      IndCol6 = index_col(@tablename, i.indid, 6),
      IndCol7 = index_col(@tablename, i.indid, 7),
      IndCol8 = index_col(@tablename, i.indid, 8),
      IndCol9 = index_col(@tablename, i.indid, 9),
      IndCol10 = index_col(@tablename, i.indid, 10),
      IndCol11 = index_col(@tablename, i.indid, 11),
      IndCol12 = index_col(@tablename, i.indid, 12),
      IndCol13 = index_col(@tablename, i.indid, 13),
      IndCol14 = index_col(@tablename, i.indid, 14),
      IndCol15 = index_col(@tablename, i.indid, 15),
      IndCol16 = index_col(@tablename, i.indid, 16)
      , SegName = s.groupname
      , FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey')
      from (sysindexes i inner join
         sysfilegroups s on
         i.groupid = s.groupid )
      where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and
      (@indexname is null or i.name = @indexname) and
      (INDEXPROPERTY(object_id(@tablename), i.name, N'IsStatistics') <> 1) and
      (INDEXPROPERTY(object_id(@tablename), i.name, N'IsAutoStatistics') <> 1) and
      (INDEXPROPERTY(object_id(@tablename), i.name, N'IsHypothetical') <> 1)
      order by i.indid
   end else begin
      /* select (case when (i.status & 0x0040) != 0 then substring(i.name, 9, (datalength(i.name)/2)-17) else i.name end), i.status, i.indid, i.OrigFillFactor, */
      select i.name, i.status, i.indid, i.OrigFillFactor,
      IndCol1 = index_col(@tablename, i.indid, 1),
      IndCol2 = index_col(@tablename, i.indid, 2),
      IndCol3 = index_col(@tablename, i.indid, 3),
      IndCol4 = index_col(@tablename, i.indid, 4),
      IndCol5 = index_col(@tablename, i.indid, 5),
      IndCol6 = index_col(@tablename, i.indid, 6),
      IndCol7 = index_col(@tablename, i.indid, 7),
      IndCol8 = index_col(@tablename, i.indid, 8),
      IndCol9 = index_col(@tablename, i.indid, 9),
      IndCol10 = index_col(@tablename, i.indid, 10),
      IndCol11 = index_col(@tablename, i.indid, 11),
      IndCol12 = index_col(@tablename, i.indid, 12),
      IndCol13 = index_col(@tablename, i.indid, 13),
      IndCol14 = index_col(@tablename, i.indid, 14),
      IndCol15 = index_col(@tablename, i.indid, 15),
      IndCol16 = index_col(@tablename, i.indid, 16)
      , SegName = s.groupname
      , FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey')
      from (sysindexes i inner join
         sysfilegroups s on
         i.groupid = s.groupid )
      where id = object_id(@tablename) and i.indid > 0 and i.indid < 255
      and (@indexname is null or i.name = @indexname)
      order by i.indid
      /* order by i.name */
   end

go
/* End sp_MShelpindex */

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print N''
print N'Dumping transaction log...'
print N''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*******************************************************************************/
print N''
print N'Creating sp_MShelptype'
print N''
go
create procedure sp_MShelptype
@typename nvarchar(517) = null, @flags nvarchar(10) = null
as
    if (@typename = N'?')
    begin
        print N''
        print N'Usage:  sp_MShelptype @typeename = null, @flags nvarchar(10) = null'
        print N' where @flags is either:'
        print N' sdt        = look in system datatypes'
        print N' uddt      = look in user defined datatypes'
        print N' null    = look wherever its found'
        print N''
        return 0
    end

    /* Catch typos... */
    if (@flags is not null and @flags not in (N'sdt', N'uddt'))
        select @flags = null

    /* Find out what type we're gonna be looking in, if they gave us a name. */
    if (@typename is not null)
    begin
        declare @xusertype int
        select @xusertype = xusertype from systypes where name = @typename
        if (@xusertype is not null)
        begin
            if (@xusertype < 257)
            begin
                if (@flags is null)
                    select @flags = N'sdt'
                if (@flags != N'sdt')
                    select @xusertype = null
            end else begin
                if (@flags is null)
                    select @flags = N'uddt'
                if (@flags != N'uddt')
                    select @xusertype = null
            end
        end
        if (@xusertype is null)
        begin
            RAISERROR (15001, -1, -1, @typename)
            return 1
        end
    end

    /* Now go get the info, depending on the type they gave us. */
    if (@flags is null or @flags = N'sdt')
    begin
        /* Exclude the 'xxxxn' dblib-specific nullable types, and hardcode a check for variable length and numeric usertypes. */
      /* 7.0 ifvarlen_max returns length for all the datatypes */
        select     SystemDatatypeName = t.name,
                ifvarlen_max = y.length,
                    -- timestamp allows nulls even though the system tables say it doesn't.
                allownulls = case when t.name in (N'timestamp') then 1 else t.allownulls end,
                isnumeric = case when t.name in (N'decimal', N'numeric') then 1 else 0 end,
                allowidentity = case when t.name in (N'decimal', N'int', N'numeric', N'smallint', N'tinyint') then 1 else 0 end,
            variablelength = t.variable,
            max_len = t.length, prec_len = t.prec
         from systypes t, systypes y
         where t.xusertype < 257 and t.name not in (N'datetimn', N'decimaln', N'floatn', N'intn', N'moneyn', N'numericn') and (@typename is null or t.name = @typename)
         and y.xusertype =* t.xusertype and y.name in ( N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar' )
            order by t.name
    end

    /* Need a temp table so we can ownerqualify nonNULL rules/defaults. */
    create table #sphelptype (
        dt_xusertype        int                NULL,
        dt_basetype        nvarchar(128)    NULL,
        dt_rul             int                NULL,
        dt_def             int                NULL,

        dt_rulowner        nvarchar(128)        NULL,
        dt_rulname         nvarchar(128)        NULL,
        dt_defowner        nvarchar(128)        NULL,
        dt_defname        nvarchar(128)        NULL,
        dt_flags        int                NULL
    )

    if (@flags is null or @flags = N'uddt')
    begin
        set nocount on
        insert #sphelptype (dt_xusertype, dt_basetype, dt_rul, dt_def, dt_flags)
            select t.xusertype,
            (select distinct b.name from systypes b where b.xtype = t.xtype and b.xusertype < 257 and b.name not in (N'sysname', N'timestamp')),
            t.domain, t.tdefault, 0
            from systypes t
            where t.xusertype > 256 and (@typename is null or t.name = @typename)

        /* Make a nice, presentable qualified rule/default name for those which are non-null */
      update #sphelptype set dt_defowner = user_name(d.uid)
            from #sphelptype c, sysobjects d where c.dt_def is not null and d.id = c.dt_def
      update #sphelptype set dt_defname = d.name
            from #sphelptype c, sysobjects d where c.dt_def is not null and d.id = c.dt_def

      update #sphelptype set dt_rulowner = user_name(r.uid)
            from #sphelptype c, sysobjects r where c.dt_rul is not null and r.id = c.dt_rul
      update #sphelptype set dt_rulname =  r.name
            from #sphelptype c, sysobjects r where c.dt_rul is not null and r.id = c.dt_rul

        /* For scripting, set the dt_flags -- these apply to the BASE datatype. */
        update #sphelptype set dt_flags = dt_flags | 0x0001 where dt_basetype in ( N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar')
        update #sphelptype set dt_flags = dt_flags | 0x0002 where dt_basetype in (N'numeric', N'decimal')

        set nocount off
        select distinct UserDatatypeName = t.name,
                owner = user_name(t.uid),
                basetypename = (select distinct b.name from systypes b where b.name = s.dt_basetype),
                defaultname = dt_defname,
                rulename = dt_rulname,
                tid = t.xusertype,
                length = case when s.dt_basetype in (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') then t.length else 0 end,
                nullable = t.allownulls,
                dt_prec = case when s.dt_basetype in (N'numeric', N'decimal') then t.prec else null end,
                dt_scale = case when s.dt_basetype in (N'numeric', N'decimal') then t.scale else null end,
                dt_flags,
                allowidentity = case when (s.dt_basetype in (N'decimal', N'int', N'numeric', N'smallint', N'tinyint') and t.scale = 0) then 1 else 0 end,
            variablelength = t.variable,
            /* char count for string datatype, byte count for others */
                maxlen = case when s.dt_basetype in (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') then t.prec else t.length end,
            defaultowner = dt_defowner,
            ruleowner = dt_rulowner
            from systypes t, #sphelptype s
            where t.xusertype > 256 and (@typename is null or t.name = @typename)
                and dt_xusertype = t.xusertype
            order by t.name
    end
go
/* End sp_MShelptype */

/*******************************************************************************/
print N''
print N'Creating sp_MSdependencies'
print N''
go

create procedure sp_MSdependencies
@objname nvarchar(517) = null, @objtype int = null, @flags int = 0x01fd, @objlist nvarchar(128) = null
as
    if (@objname = N'?')
    begin
        print N'sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd'
        print N'  name:  name or null (all objects of type)'
        print N'  type:  type number (see below) or null'
        print N'      if both null, get all objects in database'
        print N'  flags is a bitmask of the following values:'
        print N'      0x10000  = return multiple parent/child rows per object'
        print N'      0x20000  = descending return order'
        print N'      0x40000  = return children instead of parents'
        print N'      0x80000  = Include input object in output result set'
        print N'      0x100000 = return only firstlevel (immediate) parents/children'
        print N'      0x200000 = return only DRI dependencies'
        print N'      power(2, object type number(s))  to return in results set:'
        print N'          0 (1      - 0x0001)     - datatype'
        print N'          1 (2      - 0x0002)     - system tables or MS-internal objects'
        print N'          2 (4      - 0x0004)     - view'
        print N'          3 (8      - 0x0008)     - user table'
        print N'          4 (16        - 0x0010)     - procedure'
        print N'          5 (32        - 0x0020)     - log'
        print N'          6 (64     - 0x0040)     - default'
        print N'          7 (128    - 0x0080)     - rule'
        print N'          8 (256    - 0x0100)     - trigger'
        print N'      shortcuts:'
        print N'          29     (0x011c) - trig, view, user table, procedure'
        print N'          448    (0x00c1) - rule, default, datatype'
        print N'          509    (0x01fd) - all but systables/objects'
        print N'          511    (0x01ff) - all'
        return 0
    end

    /* If this proc is called in a tight loop, it tends to fill up the log in a small tempdb too fast */
    /* for the trunc. log on chkpt thread to keep up.  So help it out here.                           */
   /* I can do this only if the current login has the proper permission to dump tempdb               */
   /* In order to find out this information, I need to switch to tempdb                              */
    declare @origdb nvarchar(128)
   declare @tempdbName nvarchar(258)
    select @origdb = db_name()
   SELECT @tempdbName = REPLACE(@origdb, N']', N']]')
   exec (N'if (has_dbaccess(''tempdb'') = 1) begin use tempdb if (permissions() & 0x80 <> 0) dump tran tempdb with no_log use [' + @tempdbName + N'] end')

    /* If they want SQLDMODep_DRIOnly, remove all but usertable objects from @flags */
    if (@flags & 0x200000 <> 0)
        select @flags = (@flags & ~convert(int, 0x01ff)) | power(2, 3)

    if (@objtype in (0, 5, 6, 7))
    begin
        /* Print only, do not raiserror as we may be calling this blindly and this is not a real error. */
        print N'Rules, defaults, and datatypes do not have dependencies.'
        return (0)
    end

    /*
     * Create #t1 and #t2 as temp object holding areas.  Columns are:
     *     tid        - temp object id
     *     ttype     - temp object type
     *     pid        - parent or child object id
     *     ptype     - parent or child object type
     *     bDone     - NULL means dependencies not yet evaluated, else nonNULL.
     */
    declare @curid int, @curcat int, @rowsaffected int
    declare @allobjs int
    declare @delinputobj int
    select @allobjs = 0, @delinputobj = 0, @curid = NULL, @curcat = NULL
    create table #t1 (
        tid                int                NULL,
        ttype            smallint        NULL,
        tcat            smallint        NULL,
        pid                int                NULL,
        ptype            smallint        NULL,
        pcat            smallint        NULL,
        bDone            smallint        NULL
    )
    create table #t2 (
        tid                int                NULL,
        ttype            smallint        NULL,
        tcat            smallint        NULL,
        pid                int                NULL,
        ptype            smallint        NULL,
        pcat            smallint        NULL,
        bDone            smallint        NULL
    )
    create table #tempudt (
        dtype            int                NOT NULL
    )

    /* Worktables we'll use for optimization. */
    create table #t3 (
        tid                int                NOT NULL
    )
    create table #t4 (
        tid                int                NOT NULL
    )
    /* create clustered index #ci_t3 on #t3(tid) with allow_dup_row */
    /* create clustered index #ci_t4 on #t4(tid) with allow_dup_row */
    create clustered index #ci_t3 on #t3(tid)
    create clustered index #ci_t4 on #t4(tid)
    create table #temptrig(
        id                int                NOT NULL,
        deltrig            int                NOT NULL,
        sysstat            smallint        NOT NULL,
        category        int                NOT NULL
    )
    /* create clustered index #ci_temptrig on #temptrig (deltrig) with allow_dup_row */
    create clustered index #ci_temptrig on #temptrig (deltrig)

    /*
     * If both name and type are null, this means get every object in the
     * database matching the specification they passed in.  Otherwise,
     * find the passed object or all objects of the passed type.  Start off
     * loading parent info (pid, tid); these will be put into child as needed.
     * If Objlist is specified we simply load its contents into #t1.
     */
    if (@objlist is not null)
    begin
        declare @mscategory nvarchar(12)
        select @mscategory = ltrim(str(convert(int, 0x0002)))

        exec(N'insert #t1 (pid, ptype, pcat) select l.objid, l.objtype, o.category &' +  @mscategory +
                N' from ' + @objlist + N' l, sysobjects o where o.id = l.objid ')
    end else begin
        if (@objname is null and @objtype is null)
        begin
            set nocount on
            select @allobjs = 1
            insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, o.category & 0x0002 from sysobjects o
                where ((power(2, o.sysstat & 0x0f) & 0x01ff) <> 0) and (OBJECTPROPERTY(o.id, N'IsDefaultCnst') <> 1 and OBJECTPROPERTY(o.id, N'IsRule') <> 1 )
        end else begin
            if (@objname is not null)
            begin
                select @curid = id, @objtype = o.sysstat & 0x0f, @curcat = o.category & 0x0002 from sysobjects o where id = object_id(@objname)
                if (@curid is null)
                begin
                    RAISERROR (15001, -1, -1, @objname)
                    return 1
                end
                if (@flags & 0x80000 = 0)
                    select @delinputobj = @curid
            end

            set nocount on
            if (@curid is null)
                insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, o.category & 0x0002 from sysobjects o
                    where o.sysstat & 0x0f = @objtype
            else
                insert #t1 (pid, ptype, pcat) values (@curid, @objtype, @curcat)
        end
    end
    /*
     * All initial objects are loaded as parents/children.  Now we loop, creating
     * rows of child/parent relationships.  Use #t2 as a temp area for the selects
     * to simulate recursion; when they find no rows, we're done with this step.
     *
     * Note that triggers are weird; they're part of a table definition but can
     * also reference other tables, so we need to evaluate them both ways.  SQL
     * Server stores the table for a trigger object as its deltrig; if a trigger
     * references another table, that relationship is stored in sysdepends.
     * This peculiarity of triggers requires separating the object-retrieval pass
     * from the creation-sequence pass (below).  Also, the fact that trigger tables
     * are stored in a non-indexed column (deltrig) requires us to use a worktable
     * if we're returning triggers, so we don't continually tablescan sysobjects.
     */

    if (@flags & power(2, 8) != 0)
        insert #temptrig select d.id, d.deltrig, d.sysstat, d.category from sysobjects d where OBJECTPROPERTY(d.id, N'IsTrigger') = 1

    while (select count(*) from #t1 where bDone is null) > 0
    begin
        /*
         * Remove Microsoft-internal or other system objects from #t1, unless
         * @flags specified including system tables.  We do this here so that
         * cascaded system dependencies are not included unless specifically
         * requested.  For other restrictions, we wait until below so that all
         * cascaded object types are fully evaluated.
         */
        if (@flags & power(2, 1) = 0)
            delete #t1 where ttype = 1 or tcat = 0x0002 or pcat = 0x0002

        if (@flags & 0x40000 != 0)
        begin
            if (@flags & 0x200000 = 0) begin
                /* Table --> Triggers */
                if (@flags & power(2, 8) != 0)
                    insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
                        select distinct t.pid, t.ptype, t.pcat, o.id, o.sysstat & 0x0f, o.category & 0x0002 from #t1 t, #temptrig o
                            where t.bDone is null and t.ptype = 3 and o.deltrig = t.pid

                /* Object --> sysdepends children */
                insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
                    select distinct t.pid, t.ptype, t.pcat, d.id, o.sysstat & 0x0f, o.category & 0x0002
                    from #t1 t, sysdepends d, sysobjects o
                    where t.bDone is null and d.depid = t.pid and d.id = o.id
            end

            /* Object --> sysreferences children (FK tables referencing this one) */
            insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
                select distinct t.pid, t.ptype, t.pcat, r.fkeyid, o.sysstat & 0x0f, o.category & 0x0002
                from #t1 t, sysreferences r, sysobjects o
                where t.bDone is null and r.rkeyid = t.pid and r.fkeyid = o.id
        end else begin
            if (@flags & 0x200000 = 0) begin
                /* Trigger --> Table */
                if (@flags & power(2, 3) != 0)
                    insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
                        select distinct t.pid, t.ptype, t.pcat, o.deltrig, u.sysstat & 0x0f, u.category & 0x0002
                              from #t1 t, sysobjects o, sysobjects u
                            where t.bDone is null and t.ptype = 8 and o.id = t.pid and o.deltrig != 0 and u.id = o.deltrig

                /* Object --> sysdepends parents */
                insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
                    select distinct t.pid, t.ptype, t.pcat, d.depid, o.sysstat & 0x0f, o.category & 0x0002
                    from #t1 t, sysdepends d, sysobjects o
                    where t.bDone is null and d.id = t.pid and d.depid = o.id
            end

            /* Object --> sysreferences parents (PK/UQ tables referenced by one) */
            insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
                select distinct t.pid, t.ptype, t.pcat, r.rkeyid, o.sysstat & 0x0f, o.category & 0x0002
                from #t1 t, sysreferences r, sysobjects o
                where t.bDone is null and r.fkeyid = t.pid and r.rkeyid = o.id
        end

        /*
         * We have this generation of parents in #t2, so clear the current
         * child generation's bDone flags.  Then insert from #t2; the current
         * parent generation becomes the next loop's child generation, with
         * bDone = null until next loop's dependencies are selected.
         */
        update #t1 set bDone = 1
        insert #t1 select * from #t2 where #t2.tid not in
            (select tid from #t1 where #t1.tid = #t2.tid and #t1.pid = #t2.pid)
        truncate table #t2

        /* If they only want one level, we're done.    */
        if (@flags & 0x100000 <> 0)
            update #t1 set bDone = 1
    end

    /*
     * The inner loop above did not put parents with no parents into the
     * child (tid) list.  Do that now, then remove all rows where tid is
     * NULL, because these were initial objects which now have a tid row.
     * Just in case, remove self-refs from #t1, and also remove rows from #t1
     * with NULL pid if a row exists for that tid where the pid is nonNULL.
     * Avoid nested self-joins by using worktables.
     */
    truncate table #t3
    insert #t3 select tid from #t1 where tid is not null
        and tid <> pid                -- make sure self-refs with no other refs go in child list
    /* update statistics #t3 #ci_t3 */
    insert #t1 (tid, ttype, tcat, bDone) select distinct pid, ptype, pcat, 0 from #t1 t
        where t.pid is not null and not exists (select * from #t3 where tid = t.pid)
    delete #t1 where tid = pid        -- now remove self-refs

    /*
     * Because triggers can go in both directions, we'll need to check for
     * circular dependencies on parent evaluation.  Since any tables referenced
     * by the trigger must exist before the trigger can be created, remove rows
     * where the trigger is the parent.
     */
    if (@flags & 0x40000 = 0)
        delete #t1 where ptype = 8

    truncate table #t3
    insert #t3 select tid from #t1 where tid is not null and pid is not null
    /* update statistics #t3 #ci_t3 */
    delete #t1 where #t1.tid is null or #t1.tid = #t1.pid
        or (#t1.pid is null and exists (select * from #t3 where tid = #t1.tid))

    /*
     * If we're to get all objects, get all UDDTs (which aren't in sysobjects)
     * and Rules/Defaults, assuming we're returning those types.
     */
    if (@allobjs <> 0)
    begin
        if (@flags & power(2, 0) != 0)
            insert #tempudt
                select xusertype from systypes where xusertype > 256
        if (@flags & (power(2, 7) | power(2, 6)) != 0)
            insert #t2 (tid, ttype, tcat)
                select id, sysstat & 0x0f, 0 from sysobjects
                where (OBJECTPROPERTY(id, N'IsRule') = 1 or OBJECTPROPERTY(id, N'IsDefault') = 1)
                and category & 0x0800 = 0
    end else begin
        /*
         * Not getting all objects.  Get any datatypes that
         * are referenced by objects in #t1.  We don't care about specific
         * datatype dependencies, we just want to know which ones are needed.
         */
        if (@flags & power(2, 0) != 0)
            insert #tempudt select distinct xusertype from syscolumns
                where xusertype > 256 and id in (select tid from #t1)

        /*
         * Load rules and defaults needed by datatypes and other #t1 objects
         * into #t2.  Don't track specific object dependencies with these;
         * we just want to know which ones are needed.  For defaults only, eliminate
         * those which are constraints.
         */
        if (@flags & power(2, 7) != 0)
        begin
            insert #t2 (tid, ttype, tcat)
                select distinct s.domain, 7, 0 from systypes s, #tempudt t
                    where s.domain != 0 and s.xusertype = t.dtype
                        and s.domain not in (select tid from #t1)
            insert #t2 (tid, ttype, tcat)
                select distinct s.domain, 7, 0 from syscolumns s, #t1 t
                    where s.domain != 0 and s.id = t.tid
                        and s.domain not in (select tid from #t1)
        end
        if (@flags & power(2, 6) != 0)
        begin
            insert #t2 (tid, ttype, tcat)
                select distinct s.tdefault, 6, 0 from systypes s, #tempudt t
                    where s.tdefault != 0 and s.xusertype = t.dtype
                        and s.tdefault not in (select tid from #t1)
                        and s.tdefault not in (select id from sysobjects where category & 0x0800 != 0)
            insert #t2 (tid, ttype, tcat)
                select distinct s.cdefault, 6, 0 from syscolumns s, #t1 t
                    where s.cdefault != 0 and s.id = t.tid
                        and s.cdefault not in (select tid from #t1)
                        and s.cdefault not in (select id from sysobjects where category & 0x0800 != 0)
        end
    end        /* Not getting all objects */

    /*
     * Now that we've got all objects we want, eliminate those we don't
     * want to return.  If @inputobj and they don't want it returned,
     * remove it from the table.  Then eliminate object types they don't
     * want returned.  Make sure that in doing so we retain all parent
     * objects of the types we do want -- it is possible at this point
     * that a tid we want has no rows except those with pids we don't want.
     */
    if (@flags & 0x01ff != 0x01ff or @delinputobj != 0)
    begin
        delete #t1 where @flags & power(2, ttype) = 0 or tid = @delinputobj

        /*
         * Be sure that the insert does not duplicate rows that will survive the
         * following delete -- these are rows where the pid is not @delinputobj
         * and ptype is either null or a type we'll keep (if ptype is null then
         * pid hasn't been set so no need for more complex checking).
         */
        insert #t1 (tid, ttype, tcat) select distinct tid, ttype, tcat from #t1
            where (@flags & power(2, ptype) = 0 or pid = @delinputobj)
                and tid not in (select tid from #t1 where ptype is null or
                    (pid != @delinputobj and @flags & power(2, ptype) != 0))
        delete #t1 where @flags & power(2, ptype) = 0 or pid = @delinputobj
    end

    /*
     * To determine creation order, find all objects which are not yet bDone
     * and have no parents or whose parents are all bDone, and set their bDone
     * to the next @curid.  This will leave bDone as the ascending order in
     * which objects must be created (topological sort).  Again, use worktables
     * to remove nested self-joins.
     */
    update #t1 set bDone = 0
    select @curid = 1, @rowsaffected = 1
    while (@rowsaffected <> 0)
    begin
        if (@flags & 0x40000 != 0) begin
            truncate table #t3
            insert #t3 select pid from #t1 where pid is not null and bDone = 0
            /* update statistics #t3 #ci_t3 */
            update #t1 set bDone = @curid where bDone = 0 and tid not in (select tid from #t3)
        end else begin
            truncate table #t3
            truncate table #t4
            insert #t3 select tid from #t1 where bDone = 0                /* Parents not yet done */
            /* update statistics #t3 #ci_t3 */
            insert #t4 select tid from #t1                                /* TIDs with (parents not yet done) */
                where pid is not null and pid in (select tid from #t3)
            /* update statistics #t4 #ci_t4 */
            update #t1 set #t1.bDone = @curid where #t1.bDone = 0         /* TIDs who are not (TIDs with (parents not yet done)) */
                and not exists (select * from #t4 where tid = #t1.tid)
        end
        select @rowsaffected = @@rowcount, @curid = @curid + 1
    end

    /* For SQL60 only, we need to check circular dependencies (DRI for tables is the only way to get them). */
    /* This will have occurred if we still have any rows in #t1 where bDone = 0, after the above loop. */
    /*
     * At this point, these are indirect (a->b->...->a), and can only be created by:
     *     create table a; create table b ref a; alter table a ref b
     * There is thus no way to create the tables in a single pass.  Further, the ALTER
     * TABLE B must be done AFTER data has been added (else the PK/FK will fail).
     * Therefore, the two-step model of
     *  - Create tables (and other objects)
     *  - Transfer data
     * does not work, so assume anyone doing this will do it in three passes (e.g. ScriptTransfer):
     *  - Create tables (and other objects) but no references (also defer some indexing, for perfomance)
     *  - Transfer data
     *  - Create references (and deferred indexing)
     * and just set bDone for everything remaining to @curid.
     */
    if exists (select * from #t1 where bDone = 0) begin
        --select "Circular Dependencies", object_name(tid) from #t1 where bDone = 0
        --RAISERROR (14300, -1, -1)
        --return 1
        update #t1 set bDone = @curid where bDone = 0
    end

    /*
     * Finally, return the objects.  Rules/Defaults must be created first so they're returned first,
     * followed by UDDTs. followed by all other (sysdepends/DRI) dependencies.  @curid is the bDone
     * value; we need to increment the #t1 value so our multi-result-set is in the proper sequence.
     * Of course, these never have parents, so don't return them if asking for children.
     */
    if (@flags & 0x40000 = 0) begin
        select @curid = 1
        if ((@flags & (power(2, 7) | power(2, 6)) != 0) and exists (select * from #t2)) begin
            update #t1 set bDone = bDone + 1
            select distinct oType = power(2, o.sysstat & 0x0f), oRuleDefName = o.name, oOwner = user_name(o.uid), oSequence = convert(smallint, @curid)
                from sysobjects o, #t2 t
                where o.id = t.tid
                order by power(2, o.sysstat & 0x0f), o.name
            select @curid = @curid + 1
        end
        if ((@flags & power(2, 0) != 0) and exists (select * from #tempudt)) begin
            update #t1 set bDone = bDone + 1
            select distinct oType = power(2, 0), oUDDTName = c.name, oOwner = user_name(c.uid), oSequence = convert(smallint, @curid)
                from systypes c, #tempudt t, sysobjects p
                where c.xusertype = t.dtype
                order by c.name
            select @curid = @curid + 1
        end
    end

    /*
     * Select dependency-style objects, returning parents if desired.
     * Normally sorting is in terms of who must be created first, i.e. ascending:  parent-->child-->grandchild.
     * Descending order (child-->parent-->grandparent) would be used for a graphical-dependencies evaluator showing
     * the parents.  Therefore we invert bDone if descending sort.  bDone is 1-based; min + max - bDone gives inversion.
     * Note:  Always return at least this empty set.
     */
    if (@flags & 0x20000 != 0) begin
        select @curid = max(bDone) + min(bDone) from #t1
        update #t1 set bDone = convert(smallint, @curid) - bDone
    end
    if (@flags & 0x10000 != 0)
        select distinct oType = power(2, o.sysstat & 0x0f), oObjName = o.name, oOwner = user_name(o.uid),
/*                RelType = power(2, p.sysstat & OBJTYPE_BITS), RelName = p.name, RelOwner = user_name(p.uid), */
                RelType = case when (p.name is not null) then power(2, p.sysstat & 0x0f) else 0 end, RelName = p.name, RelOwner = user_name(p.uid),
                oSequence = t.bDone
            from sysobjects o, sysobjects p, #t1 t
            where o.id = t.tid and p.id =* t.pid
            order by t.bDone, power(2, o.sysstat & 0x0f), o.name
    else
        select distinct oType = power(2, o.sysstat & 0x0f), oObjName = o.name, oOwner = user_name(o.uid),
                oSequence = t.bDone
            from sysobjects o, #t1 t
            where o.id = t.tid
            order by t.bDone, power(2, o.sysstat & 0x0f), o.name

go
/* End sp_MSdependencies */

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print N''
print N'Dumping transaction log...'
print N''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*******************************************************************************/
print N''
print N'Creating sp_MStablespace'
print N''
go

create procedure sp_MStablespace
@name nvarchar(517), @id int = null
as
    declare @rows int, @datasizeused int, @indexsizeused int, @pagesize int
    declare @dbname nvarchar(128)
    select @dbname = db_name()

    if (@id is null)
        select @id = id from sysobjects where id = object_id(@name) and (OBJECTPROPERTY(id, N'IsTable') = 1)
    if (@id is null)
    begin
        RAISERROR (15009, -1, -1, @name, @dbname)
        return 1
    end

    /* rows */
    SELECT @rows = rows
        FROM sysindexes
        WHERE indid < 2 and id = @id

    /* data */
    SELECT @datasizeused =
    (SELECT sum(dpages)
     FROM sysindexes
     WHERE indid < 2 and id = @id)
    +
    (SELECT isnull(sum(used), 0)
     FROM sysindexes
     WHERE indid = 255 and id = @id)

   /* Do not consider 2 < indid < 255 rows, those are nonclustered indices, and the space used by them are included by indid = 0(table) */
   /* or indid = 1(clustered index) already.  indid = 0(table) and = 1(clustered index) are mutual exclusive */
    /* index */
    SELECT @indexsizeused =
    (SELECT sum(used)
     FROM sysindexes
     WHERE indid in (0, 1, 255) and id = @id)
     - @datasizeused

    /* Pagesize on this server (sysindexes stores size info in pages) */
    select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type=N'E'

    select Rows = @rows, DataSpaceUsed = @datasizeused * @pagesize, IndexSpaceUsed = @indexsizeused * @pagesize
go

/* End sp_MStablespace */

/*******************************************************************************/
print N''
print N'Creating sp_MSindexspace'
print N''
go

CREATE PROCEDURE sp_MSindexspace
    @tablename nvarchar(517), @index_name nvarchar(258) = NULL
AS
BEGIN
  DECLARE @table_id int
  DECLARE @index_id int
  DECLARE @msg nvarchar(2000)
  DECLARE @pagesize int
  select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type=N'E'

  /* Make sure @tablename is local to the current database */
  /* Check if server is pre 7.0 */
  if (@@version NOT LIKE N'%7.%.%') begin
     IF (@tablename like N'%.%.%') AND (substring(@tablename, 1, charindex(N'.', @tablename) - 1) <> db_name())
     BEGIN
         RAISERROR (15078, -1, -1, N'')
         RETURN(1)
     END
  end

  /* Make sure that @tablename and @index_name exist, we are checking table instead of UserTable */
  SELECT @table_id = id
  FROM sysobjects
  WHERE (id = object_id(@tablename))
    AND (OBJECTPROPERTY(id, N'IsTable') = 1)
  IF (@table_id is NULL)
  BEGIN
    RAISERROR (15001, -1, -1, @tablename)
    RETURN(1)
  END
  IF (@index_name is not NULL)
  BEGIN
    SELECT @index_id = indid
    FROM sysindexes
    WHERE (name = @index_name)
      AND (id = object_id(@tablename))
    IF (@index_id is NULL)
    BEGIN
      SELECT @msg = @tablename + N'.' + @index_name
      RAISERROR (15001, -1, -1, @msg)
      RETURN(1)
    END
  END
  /* Ok, we're good to go */
  IF (user_id() = 1)
    CHECKPOINT
  IF (@index_name is NULL)
  BEGIN
    CREATE TABLE #IndexSizeTemp (
        IndexID            tinyint            NOT NULL,
        IndexName        nvarchar(128)        NOT NULL,
        IndexSize        int                NOT NULL,
        Comments        nvarchar(28)        NOT NULL
    )
    INSERT INTO #IndexSizeTemp
    SELECT indid, name, 0, N''
    FROM sysindexes
    WHERE (id = object_id(@tablename))
      AND ((indid > 0) AND (indid < 255))
    UPDATE #IndexSizeTemp
    SET IndexSize = used * @pagesize,
        Comments = N'(None)'
    FROM sysindexes si, #IndexSizeTemp ist
    WHERE (id = object_id(@tablename))
      AND (indid > 1) AND (indid < 255)
      AND (si.indid = ist.IndexID)
    UPDATE #IndexSizeTemp
    SET IndexSize = (used - dpages - isnull((SELECT sum(used)
                                             FROM sysindexes
                                             WHERE (indid > 1) AND (indid < 255)
                                               AND (id = object_id(@tablename))), 0)) * @pagesize,
        Comments = N'Size excludes actual data.'
    FROM sysindexes si, #IndexSizeTemp ist
    WHERE (id = object_id(@tablename))
      AND (indid = 1)
      AND (si.indid = ist.IndexID)
    SELECT N'Index ID' = IndexID, N'Index Name' = IndexName, N'Size (KB)' = IndexSize, Comments
    FROM #IndexSizeTemp
    ORDER BY IndexID
    DROP TABLE #IndexSizeTemp
  END
  ELSE
  BEGIN
    DECLARE @indid int
    SELECT @indid = indid
    FROM sysindexes
    WHERE (id = object_id(@tablename))
      AND (name = @index_name)
    /* The non-clustered index case */
    IF ((@indid > 1) AND (@indid < 255))
    BEGIN
      SELECT N'Size (KB)' = used * @pagesize
      FROM sysindexes
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
      RETURN(0)
    END
    /* The clustered index case */
    IF (@indid = 1)
    BEGIN
      SELECT N'Size (KB)' =
             (used - dpages - isnull((SELECT sum(used)
                                      FROM sysindexes
                                      WHERE (indid > 1) AND (indid < 255)
                                        AND (id = object_id(@tablename))), 0)) * @pagesize
      FROM sysindexes
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
    END
  END
  RETURN(0)
END
go
/* End sp_MSindexspace */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MStablerefs'
print N''
go

create procedure sp_MStablerefs
    @tablename nvarchar(517),
    @type nvarchar(20) = N'actualtables',
    @direction nvarchar(20) = N'primary',
    @reftable nvarchar(517) = null,
   @flags int = 0
as
   /* tablename: table whose references are being evaluated */
   /* type     : '[actual | all][tables | keys | keycols]'; all candidates, or only those actually referenced */
   /* direction: look for references from @tablename to 'primary' table(s), or to @tablename from 'foreign' table(s) */
   /* reftable : limit scope to this table, if non-null */
   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MStablerefs '%s', null, 'both'                             ***/

    /* @flags is for daVinci */
    if (@flags is null)
        select @flags = 0

    if (@tablename = N'?') begin
        PRINT N''
        PRINT N'sp_MStablerefs:'
        PRINT N'@tablename nvarchar(257),                    /* table whose references are being evaluated */'
        PRINT N'@type nvarchar(20) = [actualtables],        /* [[actual | all][tables | keys | keycols]]; all candidates, or only those actually referenced */'
        PRINT N'@direction nvarchar(20) = [primary],        /* look for references from @tablename to [primary] or to @tablename from [foreign], or [both] */'
        PRINT N'@reftable nvarchar(257) = null                /* limit scope to this table, if non-null */'
        return 0
    end

    if (lower(@direction) = N'both') begin
        select
         N'PK_Table' = PKT.name,
         N'FK_Table' = FKT.name,
         N'Constraint' = object_name(r.constid),
            c.status,
            cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
            cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
            cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
            cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
            cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
            cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
            cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
            cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
            cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
            cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
            cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
            cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
            cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
            cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
            cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
            cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),
            cRefCol1 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),
            cRefCol2 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),
            cRefCol3 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),
            cRefCol4 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),
            cRefCol5 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),
            cRefCol6 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),
            cRefCol7 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),
            cRefCol8 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),
            cRefCol9 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),
            cRefCol10 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),
            cRefCol11 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),
            cRefCol12 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),
            cRefCol13 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),
            cRefCol14 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),
            cRefCol15 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),
            cRefCol16 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),
            N'PK_Table_Owner' = user_name(PKT.uid),
            N'FK_Table_Owner' = user_name(FKT.uid)
      from sysreferences r, sysconstraints c, sysobjects PKT, sysobjects FKT
      where r.constid = c.constid and (@tablename is null or
         (r.rkeyid = object_id(@tablename) or r.fkeyid = object_id(@tablename)))
      and PKT.id = r.rkeyid and FKT.id = r.fkeyid
      return 0
    end /* @direction = 'both' */

    declare @id int, @refid int
    select @id = object_id(@tablename), @refid = object_id(@reftable)
    if (@tablename is not null and @id is null) begin
        RAISERROR (15001, -1, -1, @tablename)
        return 1
    end
    if (@reftable is not null and @refid is null) begin
        RAISERROR (15001, -1, -1, @reftable)
        return 1
    end

    create table #sprefs (
        id                    int                NOT NULL,     /* id of reftable */
        constid                int                NULL,         /* id of key */
        referenced            bit                NOT NULL    /* well, is it? */
    )

    declare @dotables bit, @doall bit, @doprimary bit, @docols bit
    select     @dotables = case when (@type like N'allt%' or @type like N'actualt%') then 1 else 0 end,
            @doall = case when (@type like N'all%') then 1 else 0 end,
            @doprimary = case when (@direction like N'p%') then 1 else 0 end,
            @docols = case when (@type like N'%keycol%') then 1 else 0 end

    /* If a specific @tablename specified, see if it has the kind of keys we want. */
    /* If asking for references from @tablename to 'primary', we must have an FKEY; */
    /* if asking for references to @tablename from 'foreign', we must have an active REFerence. */
    if (@id is not null) begin
        declare @wantkeytype int
        select @wantkeytype = case @doprimary when 1 then 0x4 else 0x8 end
        if not exists (select * from sysobjects where id = @id and category & @wantkeytype <> 0)
            goto ReturnSet
    end

    if (@dotables = 1) begin
        if (@doprimary = 1) begin
            /* Get all candidate tables (those with Primary/Unique keys in sysconstraints). */
            insert #sprefs
                select distinct id, null, 0 from sysconstraints where status & 0x0f in (1, 2)

            /* Update the referenced bit if this table references it. */
            update #sprefs set referenced = 1
                where id in (select rkeyid from sysreferences where fkeyid = @id)
        end else begin
            /* All user tables are foreign-key candidate tables. */
            insert #sprefs
                select distinct id, null, 0 from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

            /* Update the referenced bit if it references this table. */
            update #sprefs set referenced = 1
                where id in (select fkeyid from sysreferences where rkeyid = @id)
        end    /* direction */

    end else begin    /* keys */
        if (@doprimary = 1) begin
            /* Get all candidate tables (those with Primary/Unique keys in sysconstraints) and the keys. */
            insert #sprefs
                select distinct id, constid, 0 from sysconstraints where status & 0x0f in (1, 2)

            /* Follow r.rkeyindid back to sysindexes to get the name and then 'rconstid' to see if this table references it. */
         update #sprefs set referenced = 1 from #sprefs s, sysreferences r, sysindexes i
            where r.fkeyid = @id
            and i.id = r.rkeyid and i.indid = r.rkeyindid and i.status & 0x1800 <> 0
            and s.constid = object_id(N'[' + REPLACE(i.name, N']', N']]') + N']')

        end else begin
            /* First add tables with FOREIGN keys defined. */
            insert #sprefs
                select distinct id, constid, 0 from sysconstraints where status & 0x0f in (3)

            /* All user tables are foreign-key candidate tables, so add any tables we haven't yet, if @doall. */
            /* (This would be used for 'push' key definition; defining FK's from the standpoint of the PK table). */
            insert #sprefs
                select distinct id, null, 0 from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
                    and @doall = 1 and id not in (select id from #sprefs)

            /* Update the referenced bit if it references this table. */
            update #sprefs set referenced = 1
                where constid in (select constid from sysreferences where rkeyid = @id)
        end    /* direction */
    end    /* tables or keys */

    /* Exclude system and MS-internal objects, or tables/keys that aren't in the @reftable we want, if any specified. */
    delete #sprefs where id in (select id from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') <> 1 or category & 0x0002 <> 0)
            or (@refid is not null and id != @refid)

    /* Output */
ReturnSet:
    if (@docols = 0) begin
        if (@tablename is not null) begin
            select candidate_table = N'[' + REPLACE(user_name(o.uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
               candidate_key = case @dotables when 1 then N'N/A' else object_name(s.constid) end, s.referenced
               from #sprefs s, sysobjects o where o.id = s.id and (@doall = 1 or s.referenced = 1)
               order by object_name(o.id), user_name(o.uid), object_name(s.constid)
      end else begin
            select candidate_table = N'[' + REPLACE(user_name(o.uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
               candidate_key = case @dotables when 1 then N'N/A' else object_name(s.constid) end
               from #sprefs s, sysobjects o where o.id = s.id
               order by object_name(o.id), user_name(o.uid), object_name(s.constid)
      end
    end else begin    /* @docols = 1 */
        /* This is currently just implemented for 'nonNULLtablename', 'actualkeycols', 'foreign'. */
         select candidate_table = N'[' + REPLACE(user_name(o.uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
               candidate_key = object_name(s.constid),
               cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
               cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
               cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
               cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
               cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
               cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
               cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
               cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
               cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
               cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
               cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
               cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
               cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
               cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
               cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
               cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16))
            from #sprefs s, sysobjects o, sysreferences r
            where o.id = s.id and r.constid = s.constid and s.referenced = 1
            order by object_name(o.id), user_name(o.uid), object_name(s.constid)
    end
go
/* End sp_MStablerefs */


/*******************************************************************************/
print N''
print N'Creating sp_MStablekeys'
print N''
go

create procedure sp_MStablekeys
@tablename nvarchar(776) = null, @colname nvarchar(258) = null, @type int = null, @keyname nvarchar(517) = null, @flags int = null
as
    /* This proc returns the table's DRI keys.  @type is the type(s) of key(s) to return. */
    /* Make sure @type is only the key types (DRI_PRIMARYKEY, DRI_UNIQUE, DRI_REFERENCE). */
    if (@type is null)
        select @type = 0x000e
    else
        select @type = @type & 0x000e

    /* Flags usage:  For daVinci, to pass call thru to sp_MStablerefs. */
    if (@flags is null)
        select @flags = 0

    set nocount on
    create table #spkeys
    (
        cType                tinyint            NOT NULL,    /* key Type */
        cName                nvarchar(258)        NOT NULL,    /* key Name */
        cFlags                int                NULL,        /* e.g., 1 = clustered for PK/Unique */
        cColCount            int                NULL,        /* number of columns (or column pairs) in the key */
        cFillFactor            tinyint            NULL,        /* Fill factor of index creation */
        cRefTable            nvarchar(520)        NULL,        /* owner-qual Referenced table name for FKs */
        cRefKey                nvarchar(260)        NULL,        /* name of referenced key in referenced table */
            -- Note:  cConstID replaces the column list used in 6.0, for speed.
            -- The output set MUST replace this with either index_col(@tablename, cIndexID, 1-16) and NULL * 16
            -- (for PK/UQ) UNION col_name(r.fkeyid, r.fkey1-16) and col_name(r.rkeyid, r.rkey1-16), for SQLDMO,
            -- and these MUST BE nvarchar(132) for alignment in the SQLDMO cache structure!
        cConstID            int                NULL,        /* Reference constraint ID, if Foreign Key  */
        cIndexID            int                NULL,        /* ID of this key's index, if PK/UQ */
        cGroupName        sysname        NULL,        /* FileGroup name of this key, if PK/UQ */
        cDisabled        int                NULL,        /* 0 if enabled, 1 if disabled */
        cPrimaryFG        int                NULL,        /* 1 if primary FG, 0 otherwise */
    )

    declare @cType int, @cName nvarchar(258), @cFlags int, @cRefTable nvarchar(520), @fillfactor tinyint
    declare @objid int, @constid int, @indid int, @keycnt int, @q1 nvarchar(2000), @q2 nvarchar(2000), @objtype int, @groupname sysname
    declare @haskeytypes int, @wantkeytypes int
   declare @cDisabled int, @PrimaryFG int

    /* First see if @keyname was defined, and override @tablename and @type if so. */
    if (@keyname is not null)
    begin
         select @objid = id, @type = power(2, status & 0x0f) from sysconstraints where constid = object_id(@keyname)
         if (@objid is null)    begin
            RAISERROR (15001, -1, -1, @keyname)
            return 1
         end
         /* Now get the tablename for the index_col below */
         select @tablename = N'[' + REPLACE(user_name(uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(name, N']', N']]') + N']' from sysobjects where id = @objid
    end else begin
        /* Want all keys for this table (of @type type). */
        select @objid = id, @objtype = (case when OBJECTPROPERTY(id, N'IsTable') = 1 then 1 else 0 end), @haskeytypes = category & 0x0604
            from sysobjects where id = object_id(@tablename)
        if (@objid is null)    begin
            RAISERROR (15001, -1, -1, @tablename)
            return 1
        end
        if (@objtype <> 1)    begin
            RAISERROR (15218, -1, -1, @tablename)
            return 1
        end
        if @colname is not null and not exists (select * from syscolumns where id = @objid and name = @colname) begin
            RAISERROR (15253, -1, -1, @colname, @tablename)
            return 1
        end

        /* Skip cursor opening if we don't have any keys (of the type wanted); return a set anyway, for the cache. */
        if (@haskeytypes = 0)
            goto ReturnSet

        /* Map from the input bitmask to the category bitmask */
        select @wantkeytypes = 0
        if ((@type & power(2, 1)) <> 0)
            select @wantkeytypes = @wantkeytypes | 0x200
        if ((@type & power(2, 2)) <> 0)
            select @wantkeytypes = @wantkeytypes | 0x400
        if ((@type & power(2, 3)) <> 0)
            select @wantkeytypes = @wantkeytypes | 0x4
        if ((@haskeytypes & @wantkeytypes) = 0)
            goto ReturnSet
    end

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @sysgenname nvarchar(12), @pkstr nvarchar(12), @uqstr nvarchar(12), @fkstr nvarchar(12), @objtypebits nvarchar(12)
    select @sysgenname = ltrim(str(convert(int, 0x00020000)))
    select @pkstr = ltrim(str(convert(int, 1)))
    select @uqstr = ltrim(str(convert(int, 2)))
    select @fkstr = ltrim(str(convert(int, 3)))
    select @objtypebits = ltrim(str(convert(int, 0x0f)))

    /* Other ints we need strings for */
    declare @objidstr nvarchar(12), @typestr nvarchar(12)
    select @objidstr = ltrim(str(@objid))
    select @typestr = ltrim(str(@type))

    /* Qualifying key name. */
    declare @qualkeyname nvarchar(100)
    select @qualkeyname = null
    if (@keyname is not null) begin
      select @qualkeyname = N' and constid = object_id(''' + @keyname + N''')'
   end

    /*********************/
    /* Main cursor loop. */
    /*********************/
/*      exec(N'declare hC insensitive cursor for select constid, status & ' + @objtypebits + N', status & ' + @sysgenname +  */
    exec(N'declare hC cursor global for select constid, status & ' + @objtypebits + N', status & ' + @sysgenname +
            N' from sysconstraints where id = ' + @objidstr + N' and (' + @typestr + N' & power(2, status & 0x0f) != 0) ' + @qualkeyname)
    open hC
    fetch hC into @constid, @cType, @cFlags
    while (@@fetch_status >= 0) begin
        if (object_name(@constid) is null) begin
            raiserror 55555 N'Assert failed:  object_name(@constid) is null in sp_MStablekeys (pk/uq)'
            return 1
        end

        /* DRI_PRIMARYKEY, DRI_UNIQUE */
        if (@cType in (1, 2)) begin
            /* Get the index id enforcing this constraint. */
            select @indid = i.indid, @cName = o.name, @fillfactor = i.OrigFillFactor,
                    @cFlags = @cFlags | (case indid when 1 then 0x00000001 else 0 end),        /* test for clustered index */
               /* clustered index keys are part of non-clustered index key list, which cause incorrect sysindexes.keycnt */
                    @keycnt = case indid when 1 then keycnt else (select count(x.id) from sysindexkeys x where i.indid = x.indid and x.id = @objid) end,
               @groupname = f.groupname,
               @PrimaryFG = FILEGROUPPROPERTY( f.groupname, N'IsPrimaryFG' )
                from sysindexes i, sysobjects o, sysfilegroups f
            /* Use '=' instead of 'LIKE' in comparision, so we can handle wide card character correctly */
                where o.id = @constid and i.name = o.name and i.status & 0x1800 <> 0 and i.groupid = f.groupid
            if (@indid is null) begin
                raiserror 77777 N'Assert failed:  @indid is null in sp_MStablekeys (pk/uq)'
                return 1
            end

            /* Load our temp table. */
            insert #spkeys values (@cType, @cName, @cFlags, @keycnt, @fillfactor, null, null, null, @indid, @groupname, 0, @PrimaryFG)
        end

        /* DRI_REFERENCE */
        else if (@cType in (3)) begin
            /* Get the key column information from sysreferences. */
         select @keycnt = r.keycnt, @cName = object_name(r.constid), @cRefTable = N'[' + user_name(o.uid) + N']' + N'.' + N'[' + o.name + N']',
               @cDisabled = OBJECTPROPERTY( r.constid, N'CnstIsDisabled' )
            from sysreferences r, sysobjects o where r.constid = @constid and o.id = r.rkeyid

            /* Follow r.rkeyindid back to sysindexes to get the ref key name. */
            declare @cRefKey nvarchar(132)
            select @cRefKey = i.name, @cFlags = c.status from sysreferences r, sysindexes i, sysconstraints c
                where c.constid = r.constid and r.constid = @constid
                and i.id = r.rkeyid and i.indid = r.rkeyindid and i.status & 0x1800 <> 0

            /* Load our temp table. */
            insert #spkeys values (@cType, @cName, @cFlags, @keycnt, null, @cRefTable, @cRefKey, @constid, null, null, @cDisabled, 0)
        end        /* Key type */

        /* Get the next row. */
        fetch hC into @constid, @cType, @cFlags
    end            /* PRIMARY/UNIQUE */
    deallocate hC

    /* Now output the data */
ReturnSet:
    set nocount off
    select cType, cName, cFlags, cColCount, cFillFactor, cRefTable, cRefKey,
            cKeyCol1 = convert(nvarchar(132), index_col(@tablename, cIndexID, 1)),
            cKeyCol2 = convert(nvarchar(132), index_col(@tablename, cIndexID, 2)),
            cKeyCol3 = convert(nvarchar(132), index_col(@tablename, cIndexID, 3)),
            cKeyCol4 = convert(nvarchar(132), index_col(@tablename, cIndexID, 4)),
            cKeyCol5 = convert(nvarchar(132), index_col(@tablename, cIndexID, 5)),
            cKeyCol6 = convert(nvarchar(132), index_col(@tablename, cIndexID, 6)),
            cKeyCol7 = convert(nvarchar(132), index_col(@tablename, cIndexID, 7)),
            cKeyCol8 = convert(nvarchar(132), index_col(@tablename, cIndexID, 8)),
            cKeyCol9 = convert(nvarchar(132), index_col(@tablename, cIndexID, 9)),
            cKeyCol10 = convert(nvarchar(132), index_col(@tablename, cIndexID, 10)),
            cKeyCol11 = convert(nvarchar(132), index_col(@tablename, cIndexID, 11)),
            cKeyCol12 = convert(nvarchar(132), index_col(@tablename, cIndexID, 12)),
            cKeyCol13 = convert(nvarchar(132), index_col(@tablename, cIndexID, 13)),
            cKeyCol14 = convert(nvarchar(132), index_col(@tablename, cIndexID, 14)),
            cKeyCol15 = convert(nvarchar(132), index_col(@tablename, cIndexID, 15)),
            cKeyCol16 = convert(nvarchar(132), index_col(@tablename, cIndexID, 16)),
            cRefCol1 = convert(nvarchar(132), null),
            cRefCol2 = convert(nvarchar(132), null),
            cRefCol3 = convert(nvarchar(132), null),
            cRefCol4 = convert(nvarchar(132), null),
            cRefCol5 = convert(nvarchar(132), null),
            cRefCol6 = convert(nvarchar(132), null),
            cRefCol7 = convert(nvarchar(132), null),
            cRefCol8 = convert(nvarchar(132), null),
            cRefCol9 = convert(nvarchar(132), null),
            cRefCol10 = convert(nvarchar(132), null),
            cRefCol11 = convert(nvarchar(132), null),
            cRefCol12 = convert(nvarchar(132), null),
            cRefCol13 = convert(nvarchar(132), null),
            cRefCol14 = convert(nvarchar(132), null),
            cRefCol15 = convert(nvarchar(132), null),
            cRefCol16 = convert(nvarchar(132), null),
            cIndexID,
            cGroupName,
         cDisabled,
          cPrimaryFG
        from #spkeys where cType in (1, 2)
            and (@colname is null or
                index_col(@tablename, cIndexID, 1) = @colname or
                index_col(@tablename, cIndexID, 2) = @colname or
                index_col(@tablename, cIndexID, 3) = @colname or
                index_col(@tablename, cIndexID, 4) = @colname or
                index_col(@tablename, cIndexID, 5) = @colname or
                index_col(@tablename, cIndexID, 6) = @colname or
                index_col(@tablename, cIndexID, 7) = @colname or
                index_col(@tablename, cIndexID, 8) = @colname or
                index_col(@tablename, cIndexID, 9) = @colname or
                index_col(@tablename, cIndexID, 10) = @colname or
                index_col(@tablename, cIndexID, 11) = @colname or
                index_col(@tablename, cIndexID, 12) = @colname or
                index_col(@tablename, cIndexID, 13) = @colname or
                index_col(@tablename, cIndexID, 14) = @colname or
                index_col(@tablename, cIndexID, 15) = @colname or
                index_col(@tablename, cIndexID, 16) = @colname
            )
        UNION
        select c.cType, c.cName, c.cFlags, c.cColCount, c.cFillFactor, c.cRefTable, c.cRefKey,
            cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
            cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
            cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
            cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
            cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
            cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
            cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
            cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
            cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
            cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
            cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
            cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
            cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
            cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
            cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
            cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),
            cRefCol1 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),
            cRefCol2 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),
            cRefCol3 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),
            cRefCol4 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),
            cRefCol5 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),
            cRefCol6 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),
            cRefCol7 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),
            cRefCol8 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),
            cRefCol9 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),
            cRefCol10 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),
            cRefCol11 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),
            cRefCol12 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),
            cRefCol13 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),
            cRefCol14 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),
            cRefCol15 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),
            cRefCol16 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),
            cIndexID,
            cGroupName,
         cDisabled,
          cPrimaryFG
        from #spkeys c, sysreferences r where c.cType = 3 and r.constid = c.cConstID
            and (@colname is null or
                col_name(r.fkeyid, r.fkey1) = @colname or
                col_name(r.fkeyid, r.fkey2) = @colname or
                col_name(r.fkeyid, r.fkey3) = @colname or
                col_name(r.fkeyid, r.fkey4) = @colname or
                col_name(r.fkeyid, r.fkey5) = @colname or
                col_name(r.fkeyid, r.fkey6) = @colname or
                col_name(r.fkeyid, r.fkey7) = @colname or
                col_name(r.fkeyid, r.fkey8) = @colname or
                col_name(r.fkeyid, r.fkey9) = @colname or
                col_name(r.fkeyid, r.fkey10) = @colname or
                col_name(r.fkeyid, r.fkey11) = @colname or
                col_name(r.fkeyid, r.fkey12) = @colname or
                col_name(r.fkeyid, r.fkey13) = @colname or
                col_name(r.fkeyid, r.fkey14) = @colname or
                col_name(r.fkeyid, r.fkey15) = @colname or
                col_name(r.fkeyid, r.fkey16) = @colname
            )
        order by cType, cName

    if (@flags & 1 <> 0)
        exec sp_MStablerefs @tablename, N'actualkeycols', N'foreign'

go
/* End sp_MStablekeys */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MStablechecks'
print N''
go

create procedure sp_MStablechecks
    @tablename nvarchar(517), @flags int = null
as
   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MStablechecks '%s'                                        ***/

    declare @id int
    select @id = object_id(@tablename)
    if (@id is null) begin
        RAISERROR (15001, -1, -1, @tablename)
        return 1
    end

    /* @flags is for daVinci. */
    if (@flags is null)
        select @flags = 0

    /* We'll put out the check text if it's all in one row (most likely); otherwise leave it */
    /* blank for refetching in its entirety via sp_helptext, unless @flags wants it anyway. */
    select object_name(t.id),
        case when (@flags & 1 <> 0 or not exists (select * from syscomments where id = t.id and colid = 2))
                then t.text else null end,
        c.status & (convert(int, 0x00200000) | convert(int, 0x00020000) | convert(int, 0x00004000)),
      OBJECTPROPERTY( t.id, N'CnstIsDisabled' )
    from syscomments t, sysconstraints c
    where t.id = c.constid and c.id = @id and c.status & 0x0f = 4
        and (@flags & 1 <> 0 or t.colid = 1)
    order by object_name(t.id), t.colid
go
/* End sp_MStablechecks */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSsettopology'
print N''
go

/* Need this because it will set sysservers columns. */
sp_configure N'allow updates', 1
go
reconfigure with override
go

create procedure sp_MSsettopology
    @server nvarchar(258), @X int, @Y int
as
    update master..sysservers set topologyx = @X, topologyy = @Y
        where srvname = @server
    if (@@rowcount = 0) begin
        RAISERROR (15015, -1, -1, @server)
        return 1
    end
    return 0
go
/* End sp_MSsettopology */

sp_configure N'allow updates', 0
go
reconfigure with override
go

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSmatchkey'
print N''
go

create proc sp_MSmatchkey
    @tablename nvarchar(517),
    @col1 nvarchar(258),
    @col2 nvarchar(258) = null,
    @col3 nvarchar(258) = null,
    @col4 nvarchar(258) = null,
    @col5 nvarchar(258) = null,
    @col6 nvarchar(258) = null,
    @col7 nvarchar(258) = null,
    @col8 nvarchar(258) = null,
    @col9 nvarchar(258) = null,
    @col10 nvarchar(258) = null,
    @col11 nvarchar(258) = null,
    @col12 nvarchar(258) = null,
    @col13 nvarchar(258) = null,
    @col14 nvarchar(258) = null,
    @col15 nvarchar(258) = null,
    @col16 nvarchar(258) = null
as
    declare @id int, @ii int, @colnotfound nvarchar(258), @keycnt int
    select @id = object_id(@tablename)
    if (@id is null) begin
        RAISERROR (15001, -1, -1, @tablename)
        return 1
    end
    select @ii = 1
    create table #t1 (        /* Join into this... */
        i                    int                NOT NULL,
        name                nvarchar(258)        NULL
    )
    insert #t1 values (1, @col1)
    insert #t1 values (2, @col2)
    insert #t1 values (3, @col3)
    insert #t1 values (4, @col4)
    insert #t1 values (5, @col5)
    insert #t1 values (6, @col6)
    insert #t1 values (7, @col7)
    insert #t1 values (8, @col8)
    insert #t1 values (9, @col9)
    insert #t1 values (10, @col10)
    insert #t1 values (11, @col11)
    insert #t1 values (12, @col12)
    insert #t1 values (13, @col13)
    insert #t1 values (14, @col14)
    insert #t1 values (15, @col15)
    insert #t1 values (16, @col16)
    delete #t1 where name is null

    select @colnotfound = min(name) from #t1 where name not in (select name from syscolumns where id = @id)
    if (@colnotfound is not null) begin
        RAISERROR (15253, -1, -1, @colnotfound, @tablename)
        return 1
    end
    select @ii = 1, @keycnt = count(*) from #t1

    /* Load all indexes which have the matching number of columns into a temp table, then eliminate those which don't qualify. */
    /* Remember the RID in the nc index is counted as a key */
    create table #i1 (
        i                    int                NOT NULL
    )
    insert #i1 select indid from sysindexes where status & 0x1800 <> 0
        and id = @id and keycnt - (case indid when 1 then 0 else 1 end) = @keycnt
    while (@ii <= @keycnt) begin
        delete #i1 from #i1 i, #t1 t where t.i = @ii and index_col(@tablename, i.i, t.i) <> t.name
        select @ii = @ii + 1
    end

    /* The qualifying key will be the lowest indid (or the ONLY indid, if we disallow duplicate indexes), if any remain. */
    select name from sysindexes where id = @id and indid = (select min(i) from #i1)
go
/* End sp_MSmatchkey */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeach_worker'
print N''
go

/*
 * This is the worker proc for all of the "for each" type procs.  Its function is to read the
 * next replacement name from the cursor (which returns only a single name), plug it into the
 * replacement locations for the commands, and execute them.  It assumes the cursor "hCForEach"
 * has already been opened by its caller.
 */
create proc sp_MSforeach_worker
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null
as
    set nocount on
    declare @name nvarchar(517), @namelen int, @q1 nvarchar(2000), @q2 nvarchar(2000)
   declare @q3 nvarchar(2000), @q4 nvarchar(2000), @q5 nvarchar(2000)
    declare @q6 nvarchar(2000), @q7 nvarchar(2000), @q8 nvarchar(2000), @q9 nvarchar(2000), @q10 nvarchar(2000)
    declare @cmd nvarchar(2000), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(2000)
   declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)

    create table #qtemp (    /* Temp command storage */
        qnum                int                NOT NULL,
        qchar                nvarchar(2000)    NULL
    )
    open hCForEach
    fetch hCForEach into @name

    /* Loop for each database */
    while (@@fetch_status >= 0) begin
        /* Initialize. */

      /* save the original dbname */
      select @namesave = @name
        select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
        while (@cmd is not null) begin        /* Generate @q* for exec() */
            /*
             * Parse each @commandX into a single executable batch.
             * Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
             * We also may append @commandX's (signified by '++' as first letters of next @command).
             */
            select @replacecharindex = charindex(@replacechar, @cmd)
            while (@replacecharindex <> 0) begin

            /* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */
            /* if the name has not been single quoted in command, do not doulbe them */
            /* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
            select @name = @namesave
            select @namelen = datalength(@name)
            declare @tempindex int
            if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin
               /* if ? is inside of '', we need to double all the ' in name */
               select @name = REPLACE(@name, N'''', N'''''')
            end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin
               /* if ? is inside of [], we need to double all the ] in name */
               select @name = REPLACE(@name, N']', N']]')
            end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @tempindex = charindex(N'].[', @name)
               select @nametmp  = substring(@name, 2, @tempindex-2 )
               select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
               select @nametmp  = REPLACE(@nametmp, N']', N']]')
               select @nametmp2 = REPLACE(@nametmp2, N']', N']]')
               select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'
            end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */
               /* j.i.c., since we should not fall into this case */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @nametmp = substring(@name, 2, len(@name)-2 )
               select @nametmp = REPLACE(@nametmp, N']', N']]')
               select @name = N'[' + @nametmp + N']'
            end
            /* Get the new length */
            select @namelen = datalength(@name)

            /* start normal process */
                if (datalength(@cmd) + @namelen - 1 > 2000) begin
                    /* Overflow; put preceding stuff into the temp table */
                    if (@useq > 9) begin
                        raiserror 55555 N'sp_MSforeach_worker assert failed:  command too long'
                        close hCForEach
                        deallocate hCForEach
                        return 1
                    end
                    if (@replacecharindex < @namelen) begin
                        /* If this happened close to beginning, make sure expansion has enough room. */
                        /* In this case no trailing space can occur as the row ends with @name. */
                        select @nextcmd = substring(@cmd, 1, @replacecharindex)
                        select @cmd = substring(@cmd, @replacecharindex + 1, 2000)
                        select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
                        select @replacecharindex = charindex(@replacechar, @cmd)
                        insert #qtemp values (@useq, @nextcmd)
                        select @useq = @useq + 1
                        continue
                    end
                    /* Move the string down and stuff() in-place. */
                    /* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
                    /* In this case, the char to be replaced is moved over by one. */
                    insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
                    if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin
                        select @cmd = N' ' + substring(@cmd, @replacecharindex, 2000)
                        select @replacecharindex = 2
                    end else begin
                        select @cmd = substring(@cmd, @replacecharindex, 2000)
                        select @replacecharindex = 1
                    end
                    select @useq = @useq + 1
                end
                select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
                select @replacecharindex = charindex(@replacechar, @cmd)
            end

            /* Done replacing for current @cmd.  Get the next one and see if it's to be appended. */
            select @usecmd = @usecmd + 1
            select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
            if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin
                insert #qtemp values (@useq, @cmd)
                select @cmd = substring(@nextcmd, 3, 2000), @useq = @useq + 1
                continue
            end

            /* Now exec() the generated @q*, and see if we had more commands to exec().  Continue even if errors. */
            /* Null them first as the no-result-set case won't. */
            select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
            select @q1 = qchar from #qtemp where qnum = 1
            select @q2 = qchar from #qtemp where qnum = 2
            select @q3 = qchar from #qtemp where qnum = 3
            select @q4 = qchar from #qtemp where qnum = 4
            select @q5 = qchar from #qtemp where qnum = 5
            select @q6 = qchar from #qtemp where qnum = 6
            select @q7 = qchar from #qtemp where qnum = 7
            select @q8 = qchar from #qtemp where qnum = 8
            select @q9 = qchar from #qtemp where qnum = 9
            select @q10 = qchar from #qtemp where qnum = 10
            truncate table #qtemp
            exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
            select @cmd = @nextcmd, @useq = 1
        end /* while @cmd is not null, generating @q* for exec() */

        /* All commands done for this name.  Go to next one. */
        fetch hCForEach into @name
    end /* while FETCH_SUCCESS */
    close hCForEach
    deallocate hCForEach
    return 0
go

/* End sp_MSforeach_worker */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeachdb'
print N''
go

/*
 * The following table definition will be created by SQLDMO at start of each connection.
 * We don't create it here temporarily because we need it in Exec() or upgrade won't work.
 */

create proc sp_MSforeachdb
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
    @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via a temp table. */

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
    select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
    select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
    select @dbinaccessible = N'0x80000000'        /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */

    if (@precommand is not null)
        exec(@precommand)

    declare @origdb nvarchar(128)
    select @origdb = db_name()

    /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
   /* Create the select */
    exec(N'declare hCForEach cursor global for select name from master..sysdatabases d ' +
            N' where (d.status & ' + @inaccessible + N' = 0)' +
            N' and ((DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1)) or ' +
            N' ( DATABASEPROPERTY(d.name, ''issingleuser'') = 1 and not exists ' +
            N' (select * from master..sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))' )

    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)

   declare @tempdb nvarchar(258)
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')
   exec (N'use ' + N'[' + @tempdb + N']')

    return @retval
go
/* End sp_MSforeachdb */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeachtable'
print N''
go

create proc sp_MSforeachtable
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
    @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    /* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via a temp table. */

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @mscat nvarchar(12)
    select @mscat = ltrim(str(convert(int, 0x0002)))

    if (@precommand is not null)
        exec(@precommand)

    /* Create the select */
   exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from sysobjects o '
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
         + @whereand)
    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)

    return @retval
go
/* End sp_MSforeachtable */

/*******************************************************************************/

print N''
print N'Creating sp_MSloginmappings'
print N''
go



create proc sp_MSloginmappings
    @loginname nvarchar(258) = null, @flags int = 0
as
    /*
     * @flags bits:
     *        0x01    - current db only
     */
    /*
     * Added @dbname so dbo can see everyone in current database.
     * Use hacky 4.21 syntax so it will run there, instead of a case..when.
     */
    declare @checkmultilogin int
    select @checkmultilogin = 1
    if ((@flags & 0x01 <> 0) and user_id() = 1)
        select @checkmultilogin = 0

    declare @logincount int
    select @logincount = 0
    if (@loginname is not null)
        select @logincount = count(*) from syslogins where loginname = @loginname

    /* Gotta be sa or dbo to see other than just current login. */
    declare @numlogins int, @whereloginname nvarchar(258), @name nvarchar(258), @retval int
    if (@loginname is null)
        select @numlogins = 2
    else
        select @numlogins = count(*) from syslogins where loginname = @loginname

    if (@numlogins = 0) begin
        RAISERROR (15007, -1, -1, @loginname)        /* Login not found */
        return 1
    end
    if (@checkmultilogin <> 0) begin
      /* We do not want to allow everybody to execute this SP */
        if (is_member(N'db_ddladmin') <> 1 and is_member(N'db_owner') <> 1 and is_member(N'db_accessadmin') <> 1 and is_member(N'db_securityadmin') <> 1 and (@numlogins > 1 or suser_sid() <> suser_sid(@loginname))) begin
            RAISERROR (14301, -1, -1, N'')                /* Only sa can see other than the current login */
            return 1
        end
    end
    if (@loginname is not null)
        select @whereloginname = N' and loginname = ''' + @loginname + N''''
   else
      select @whereloginname = N' '

    /*
     * This proc returns a result set with one or more rows for each database for which a login is a user or aliased to one.
     * If loginname is specified, the results are limited to that login.  First load a temp table with all logins that are
     * in a db, then add those which aren't mapped to any db.
     */
    create table #loginmappings(
        LoginName            nvarchar(128)        NULL,
        DBName                nvarchar(128)        NULL,
        UserName            nvarchar(128)        NULL,
        AliasName            nvarchar(128)        NULL
    )
    if (@flags & 0x01 <> 0) begin
        INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL
        /*
         * We only allow multi-db on a 6.x server because dynamic exec() didn't exist before then,
         * hence there is no way to loop thru every database.  This is caught in SQLDMO so no
         * need for error message here; we'll just return no result sets.
         */
    end else begin
        exec @retval = sp_MSforeachdb
            N'use [?] INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL'
        if (@retval <> 0)
            return 1
        insert #loginmappings select l.loginname, null, null, null from master..syslogins l where l.loginname not in (select LoginName from #loginmappings) and l.loginname is not NULL
    end

    /*
     * Now bring them out by loginname, each in its own result set.
     * If this is for all logins, we'll return all logins; if for curdb,
     * only those in #loginmappings (i.e. only those mapped in curdb).
     */
    exec(N'declare hCForEachLogin cursor global for select loginname from master..syslogins where loginname is not NULL ' + @whereloginname + N' order by loginname')
    if (@@error = 0)
        open hCForEachLogin
    if (@@error <> 0)
        return @@error
    fetch hCForEachLogin into @name
    while (@@fetch_status >= 0) begin
      /* Use '=' instead of 'LIKE' in comparision, so we can handle wide card character correctly */
        if ((@flags & 0x01 = 0) or exists (select * from #loginmappings where LoginName = @name))
            select * from #loginmappings where LoginName = @name
        fetch hCForEachLogin into @name
    end /* FETCH_SUCCESS */
    close hCForEachLogin
    deallocate hCForEachLogin
    return @@error
go
/* End sp_MSloginmappings */

/*******************************************************************************/
print N''
print N'Creating sp_MSuniquename'
print N''
go

create procedure sp_MSuniquename
    @seed nvarchar(128), @start int = null
as
    /* Return a unique name for sysobjects, based on a passed-in seed. */
    set nocount on
    declare @i int, @append nvarchar(10), @seedlen int, @temp nvarchar(128), @recalcseedlen int, @seedcharlen int
    select @i = 1, @seedlen = datalength(@seed), @recalcseedlen = 1, @seedcharlen = 0
    if (@start is not null and @start >= 0)
        select @i = @start
    while 1 < 2
    begin
        /* This is probably overkill, but start at max length of seed name, leaving room under OSQL_DBLSYSNAME_SET for @append. */
        /* We'll work our way back along the string if more room needed (pathological user). */
        select @append = ltrim(str(@i)) + N'__' + ltrim(str(@@spid))
        if (@recalcseedlen = @i or @seedcharlen = 0)
        begin
            while @recalcseedlen <= @i
                select @recalcseedlen = @recalcseedlen * 10
            select @seedcharlen = @seedlen
            if ((@seedlen + datalength(@append)) > 128) begin
                select @seedlen = 128 - datalength(@append)

                /* Get the charlen of this datalength for the substring() call. */
                select @seedcharlen = @seedlen
               /* exec sp_GetMBCSCharLen @seed, @seedlen, @seedcharlen out */
            end        /* Recalc seedlen */
        end        /* Check seedlen */

        select @temp = substring(@seed, 1, @seedcharlen) + @append

        /* If I don't set a limit somewhere, it's gonna look hung -- I'd rather get a nonunique error. */
        if object_id(@temp) is null or @i > 999999        /* if increased, watch out for overflow of @recalcseedlen */
        begin
            set nocount off
            select Name = @temp, Next = @i + 1
            return 0
        end
        select @i = @i + 1
    end
go
/* End sp_MSuniquename */

/*******************************************************************************/
print N''
print N'Creating sp_MSkilldb'
print N''
go

sp_configure updat, 1
go
reconfigure with override
go

create proc sp_MSkilldb
    @dbname nvarchar(258)
as
    if (@@trancount > 0) begin
        RAISERROR (15002, -1, -1, N'sp_MSkilldb')
        return 1
    end

    if (is_member(N'db_owner') <> 1 and is_member(N'db_ddladmin') <> 1) begin
        RAISERROR (15003, -1, -1, N'')
        return 1
    end

    /* Set this db to suspect, then let dbcc dbrepair kill it for us. */
    update master..sysdatabases set status = status | 0x0100
        where name = @dbname
    if (@@rowcount = 0) begin
        declare @len int
        select @len = datalength(@dbname)
        RAISERROR (2702, -1, -1, @len, @dbname)
        return 1
    end
    dbcc dbrepair(@dbname, dropdb)
    return 0
go

sp_configure updat, 0
go
reconfigure with override
go

/* End sp_MSkilldb */

/*******************************************************************************/
print N''
print N'Creating sp_MSobjectprivs'
print N''
go

create proc sp_MSobjectprivs
    @objname nvarchar(776) = null,
    @mode nvarchar(10) = N'object',
    @objid int = null,
    @srvpriv int = null,
    @prottype int = null,
    @grantee nvarchar(258) = null,
   @flags int = 0,
   @rollup int = 0
as

    create table #objs(
        id  int NOT NULL
    )

    /* Temp table will hold output for final select */
    create table #output (
        action      int  NOT NULL,
        colid       int  NULL,
        uid         int  NOT NULL,
        protecttype int  NOT NULL,
        id          int  NOT NULL,
        grantor     int
    )

    create table #tmp(
        action   int   NOT NULL,
        uid      int   NOT NULL,
        protecttype int  NOT NULL,
    )

   /* mode    : 'object', 'user' or 'column'*/
   /*
    * Note:  This was expanded for 6.5 due to changes in sysprotects.columns usage, affecting
    * CPermission::ListPrivilegeColumns.  The following additional parameters are for this.
    */
   /* objid   : ID of the object we're querying */
   /* srvpriv : privilege that we're querying for (e.g. select) */
   /* prottype: Protect type, e.g. GRANT/REVOKE */
   /* grantee : Grantee name. */

   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MSobjectprivs '%s'                                         ***/

   /* 8.0: mode 'column', and grantee != null, we want user column level permissions for CTable/CView::ListUserColumnPermissions */
   /*      @rollup added to indicate special rollup result set for column level permission, set to 1 to roll up */

    /* @flags is for daVinci */
    if (@flags is null)
        select @flags = 0

    /* If @objid is not null, this is for the new query for perm cols. */
    if (@objid is not null) begin
        select u.name, o.name, a = col_name(p.id, a.number), a.low, a.high, a.number
            from master.dbo.spt_values a, dbo.sysprotects p, dbo.sysobjects o, dbo.sysusers u
            where p.id = @objid and p.action = @srvpriv and p.protecttype = @prottype
            and p.uid = user_id(@grantee)
            and p.columns != 0x01 and o.id = p.id and u.uid = o.uid
                and convert(tinyint, substring(isnull(p.columns, 0x01), a.low, 1)) &
                    -- 6.5 changed so that the bit 0 position is an "invert the bits" indicator:
                    --        when 0, behaviour is the same as in prior versions, and other bits
                    --            indicate columns with the specified privilege
                    --        when 1, the other bits are indicate columns lacking the specified privilege
                    a.high <> (case when (substring(isnull(p.columns, 0x00), 1, 1) & 1 = 0) then 0 else a.high end)
                    and col_name(p.id, a.number) is not null
                    and a.type = N'P' and a.number <= (select count(*) from dbo.syscolumns where id = @objid) order by a
        return 0
    end

    set nocount on

    /*
     * To get around a 4.21 subquery bug where returning count(*) of 0 (for proc cols)
     * causes the result set to return no rows, we need two passes; one to get the
     * objects, and another to explicitly use a value (@cols) instead of a subquery.
     */
    declare @id int, @uid int, @cols int
    select @id = null, @uid = null
    if (@mode like N'us%') begin
       select @uid = user_id(@objname)
   end else if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
      /* 8.0, special path to get column level permissions from all objects on the specified user */
      select @uid = user_id(@grantee)
    end else begin
      select @id = object_id(@objname)
   end
    if (@id is null and @uid is null) begin
        RAISERROR (15001, -1, -1, @objname)
        return 1
    end

    /* Get a temp list of objects we're interested in.  Do not include repl_* users. */
   /* This is the original code */
   insert #objs select distinct p.id from dbo.sysprotects p
       where (@id is null or p.id = @id)
          and (@uid is null or p.uid = @uid)
       and p.action in (193, 195, 196, 197, 224, 26) and p.uid not in (16382, 16383)

    /* Use a "fake cursor" by deleting successive id's from #objs, as this must run on 4.21 */
    select @id = min(id) from #objs
    while (@id is not null) begin
        select @cols = count(*) from dbo.syscolumns c where c.id = @id
      /* sysprotects.columns is for SELECT and UPDATE, NULL if it is INSERT or DELETE, since INSERT and DELETE can not be applied to column level */
      insert #output select p.action, (case when p.columns is null then -1 else a.number end), p.uid, p.protecttype, p.id, p.grantor
         from master.dbo.spt_values a, dbo.sysprotects p
         where convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high !=0
         and (p.id = @id)
         and (@uid is null or p.uid = @uid)
         and a.number <= @cols
         and a.type = N'P'

      declare @count int, @whataction int, @whatid int, @dup int, @whatprot int

      /* First pass to correct duplicates */
      select @count = count(*) from #output where id = @id and colid in (0, -1) and protecttype in (205, 204)
      if ( @count > 0 ) begin
         /* We might have duplicate rows for permission on single coulmn(s) at this point */
         /* Use a fake cursor to remove the duplicates first. */
         insert #tmp select action, uid, protecttype from #output where id = @id and colid in (0, -1) and protecttype in (205, 204)
         select @whataction = min(action) from #tmp
         select @whatid = uid from #tmp where action = @whataction
         while (@whataction is not null) begin
            if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
               /* Special case for column level permissions on ALL objects for the specified user, we don't want the row(s) on the entire table */
               /* and we don't want the possible duplicate rows in single column(s) */
               delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction
                      and (exists (select * from #output where (@whatid = uid) and (colid in (0, -1)) and action = @whataction) and (id = @id))
               delete #output where (@whatid = uid) and (colid in (0, -1)) and (action = @whataction) and (id = @id)
            end else if (@mode like N'use%') and (@objname is not null) begin
               /* Special case for the user mode, we do want to keep the entire table permissions */
               delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction and (id = @id)
            end else begin
               /* Other cases */
               delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction
            end

            delete #tmp where @whatid = uid
            select @whataction = min(action) from #tmp
            select @whatid = uid from #tmp where action = @whataction
         end
         delete #tmp
      end

      /* Second pass to correct protect type */
      select @count = count(*) from #output where id = @id and colid in (0, -1)
      if ( @count > 0 ) begin
         /* use another fake cursor to correct the protecttype */
         /* if there are multiple rows in #output for the same id and action, and if colid = 0 exist */
         /* then other rows should have different protecttype from the one in colid = 0 row */
         insert #tmp select action, uid, protecttype from #output where id = @id and colid in (0, -1)
         select @whataction = min(action) from #tmp
         select @whatid = uid from #tmp where action = @whataction
         select @whatprot = protecttype from #tmp where uid = @whatid and action = @whataction
         while (@whataction is not null) begin
               delete #output where id = @id and colid not in (0, -1) and @whataction = action and @whatid = uid and @whatprot = protecttype
               delete #tmp where action = @whataction and @whatid = uid
               select @whataction = min(action) from #tmp
               select @whatid = uid from #tmp where action = @whataction
               select @whatprot = protecttype from #tmp where uid = @whatid and action = @whataction
         end
         delete #tmp
      end

        /* Increment our "fake cursor" column and get the next one. */
        delete #objs where id = @id
        select @id = min(id) from #objs
    end

    /*
     * Organize so that the non-collist privileges are returned first.. this allows
     * scripting to combine them.  sysprotects.action is tinyint, so the hibyte won't conflict.
     */

    update #output set action = action | 0x10000000 where colid <> 0

    /*
     *  BUG 58252
     *  Delete the columns that was droped
     */
    delete from #output where colid not in (0, -1) and col_name(id, colid) is null
    /*
     * Order output by uid so Public will script before other groups (we need to script privs for public before
     * other groups, before users; otherwise sysprotects doesn't hold onto things right).  Sub-order is by object id
     * so we know when we're done with one object and onto the next, then by protecttype to group all GRANTs and
     * REVOKEs together, and lastly by action (including ORDER_ACTION_BIT so scripting can be more efficient)
     * because we may have multiple rows for columns.
     */

    set nocount off
   if (@mode not like N'col%') begin
      /* Mode is not 'column', do the regular stuff */
       select p.action & ~convert(int, 0x10000000), N'column' = col_name(p.id, p.colid), p.uid, N'username' = user_name(p.uid),
               p.protecttype, o.name, N'owner' = user_name(o.uid), p.id, N'grantor' = user_name(p.grantor)
             from #output p, dbo.sysobjects o
             where o.id = p.id
             order by p.uid, p.id, p.protecttype, p.action
   end else
   /* Below are spcial cases for column level permissions */
   if (@objname is null) and (@grantee is not null) and (@rollup = 0) begin
      /* 8.0, special path to get column level permissions from all objects on the specified user */
      select N'ObjectName' = o.name, N'Owner' = user_name(o.uid), N'ColumnName' = col_name(p.id, p.colid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, dbo.sysobjects o
             where p.id = o.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
    end else if (@objname is not null) and (@grantee is not null) and (@rollup = 0) begin
      /* 8.0, mode 'column', and grantee != null, we want column level permissions on this object for this user */
      select N'column' = col_name(p.id, p.colid), N'owner' = user_name(o.uid), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, dbo.sysobjects o
             where o.id = p.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
   end else if (@objname is not null) and (@grantee is null) and (@rollup = 0) begin
      /* 8.0, mode 'column', and grantee = null, we want column level permissions on this object for all users */
      select N'column' = col_name(p.id, p.colid), N'owner' = user_name(o.uid), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, dbo.sysobjects o
             where o.id = p.id and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
   end else if (@objname is null) and (@grantee is not null) and (@rollup <> 0) begin
      /* 8.0, roll up version of the special path to get column level permissions from all objects on the specified user */
      select distinct N'ObjectName' = o.name, N'owner' = user_name(o.uid),
             N'Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
             N'Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
             N'Type' = p.protecttype
             from #output p, dbo.sysobjects o
             where p.id = o.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by o.name
   end else if (@objname is not null) and (@grantee is null) and (@rollup <> 0) begin
      /* 8.0, roll up version of the special path to return column level permissions on this object for all users */
      select distinct N'UserName' = user_name(p.uid),
             N'Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
             N'Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
             N'Type' = p.protecttype
             from #output p, dbo.sysobjects o
             where o.id = p.id and col_name(p.id, p.colid) is not null
             order by user_name(p.uid)
   end else begin
      raiserror 55555 N'Invalid parameter combinations.'
        return 1
   end
go
/* End sp_MSobjectprivs */


/*******************************************************************************/
/* Need to create the version proc here so we can set its category bit */
print N''
print N'Creating sp_MSSQLDMO70_version'
print N''
go

create procedure sp_MSSQLDMO70_version
as
    /* Values for this are same as @@microsoft_version:  0xrraaiibb (reserved, major, minor, build). */
    declare @i int
    select @i = 0x07000000    /* Must be in hex! */
    /* Select the numeric value, and a conversion to make it readable */
    select N'Microsoft SQLDMO Scripts' = @i, N'Version' = convert(binary(4), @i)
go

/*
 * The following two scripts must retain the SQLOLE nomenclature as we provide them to be an informative
 * notification to downlevel connections.
 */
create procedure sp_MSSQLOLE65_version
as
    RAISERROR 55555 N'You must upgrade your SQL Enterprise Manager and SQL-DMO (SQLOLE) to version 7.0 (SQLDMO) to connect to this server.'
   return 1
go

create procedure sp_MSSQLOLE_version
as
    RAISERROR 55555 N'You must upgrade your SQL Enterprise Manager and SQL-DMO (SQLOLE) to version 7.0 (SQLDMO) to connect to this server.'
   return 1
go

/*******************************************************************************/
print N''
print N'Creating sp_MSscriptdatabase'
print N''
go
create procedure sp_MSscriptdatabase
@dbname nvarchar(258)
as
   /* verify */
   declare @id int
   select @id = dbid from master..sysdatabases where name = @dbname
   if (@id is null)
   begin
      RAISERROR (15001, -1, -1, @dbname)
      return 1
   end

   /* Ready to get to work */
   declare @dbTempname nvarchar(258)
   SELECT @dbTempname = REPLACE(@dbname, N']', N']]')
   exec (N'[' + @dbTempname + N']' + N'..sp_MSscriptdb_worker ')
go
/* End sp_MSscriptdatabase */

/*******************************************************************************/
print N''
print N'Creating sp_MSscriptdb_worker'
print N''
go
create procedure sp_MSscriptdb_worker
as
    set nocount on

   declare @PageSize int;
   select @PageSize = (low/1024) from master..spt_values where number = 1 and type = N'E'

    create table #tempFG
    (
     cDefault     int,                                  /* 1 for default FG, 0 for user defined */
     cDBFile      int,                                  /* 1 for DB file, 0 for Log file */
      cSize        int,                                  /* in 8K page */
      cMaxSize     int,
      cGrowth      int,
     cGrowthType  int,                                  /* 1 for GrowthInMB, 0 for GrowthInPercent */
     cFGName      nvarchar(132) NOT NULL,      /* FG name */
      cName        nchar(132) NOT NULL,         /* Logical */
      cFileName    nchar(264) NOT NULL,        /* Physical */
    )

   /* Default FileGroup first, which should cover all the log files */
   /* This one to pick up all the db files in Primary file group, while group id = 1 */
   insert #tempFG select 1, 1, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else (o.maxsize * @PageSize)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, o.name, o.filename from sysfiles o, sysfilegroups g where g.groupid = 1 and g.groupid = o.groupid and (o.status & 0x40) = 0
   /* This one to pick up all the log files in Primary file group, while group id = 0, note that group id 0 does not exist in sysfilegroups */
   insert #tempFG select 1, 0, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else (o.maxsize * @PageSize)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), N'PRIMARY', o.name, o.filename from sysfiles o where o.groupid = 0 and (o.status & 0x40) <> 0
   /* Other FileGroups, we should have DBFiles, no log files */

    create table #tempID
   (
        cGroupID int
    )
   insert #tempID select groupid from sysfilegroups where groupid <> 1

   declare @FGid int
    exec(N'declare hC cursor global for select cGroupID from #tempID')
    open hC
    fetch hC into @FGid
    while (@@fetch_status >= 0) begin
      insert #tempFG select 0, 1, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else (o.maxsize * @PageSize)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, o.name, o.filename from sysfiles o, sysfilegroups g where g.groupid = @FGid and g.groupid = o.groupid and (o.status & 0x40) = 0
      fetch hC into @FGid
   end
    deallocate hC

   select * from #tempFG
   DROP TABLE #tempFG

go
/* End sp_MSscriptdb_worker */

/*******************************************************************************/
/*******************************************************************************/
/* exec sp_MSdbuseraccess 'perm', 'dbname' -- selecting priv bit from specified db                       */
/* exec sp_MSdbuseraccess 'db', 'dbname'   -- select databases, need to change db if dbname is specified */
/* exec sp_MSdbuseraccess 'init', 'dbname' -- noop                                                       */
/*******************************************************************************/
print N''
print N'Creating sp_MSdbuseraccess'
print N''
go

create proc sp_MSdbuseraccess
    @mode nvarchar(10) = N'perm', @qual nvarchar(128) = N'%'
as

   set nocount on

   declare @accessbit int
    if (lower(@mode) like N'perm%') begin
      /* verify */
      declare @id int, @stat int, @inval int
      select @id = dbid, @stat = status from master..sysdatabases where name = @qual
      if (@id is null) begin
         RAISERROR (15001, -1, -1, @qual)
         return 1
      end

      /* Can we access this db? */
      declare @single int
      select @single = DATABASEPROPERTY( @qual, N'issingleuser' )
/*      if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin  */
      if ((@single <> 0) or
         (DATABASEPROPERTY(@qual, N'isdetached') <> 0) or
         (DATABASEPROPERTY(@qual, N'isshutdown') <> 0) or
         (DATABASEPROPERTY(@qual, N'issuspect') <> 0) or
         (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinload') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinrecovery') <> 0) or
         (DATABASEPROPERTY(@qual, N'isnotrecovered') <> 0)) begin
         select @inval = 0x80000000
         select @inval
         return 0
      end
      select @accessbit = has_dbaccess(@qual)
      if ( @accessbit <> 1) begin
         select @inval = 0x40000000
         select @inval
         return 0
      end

      /** OK, we can access this db, need to go to the specified database to get priv bit **/
      declare @dbTempname nvarchar(258)
      declare @tempindex int
      SELECT @dbTempname = REPLACE(@qual, N']', N']]')
      exec (N'[' + @dbTempname + N']' + N'..sp_MSdbuserpriv ')
      return 0
   end

   /* If 'db', we want to know if what kind of access we have to the specified databases */
   /* If we are not in master, then we are selecting single database, we want to correct role bit to save round trip */
   if (lower(@mode) like N'db%') begin
      /* Make sure we're either in master or only doing it to current db. */
      declare @dbrole int
      select @dbrole = 0x0000

      if (db_id() <> 1)
         select @qual = db_name()

      /* If dbname contains ', double it for the cursor, since cursor statement is inside of '' */
      declare @qual2 nvarchar(128)
      SELECT @qual2 = REPLACE(@qual, N'''', N'''''')

      /* Preprocessor won't replace within quotes so have to use str(). */
      declare @invalidlogin nvarchar(12)
      select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
      declare @inaccessible nvarchar(12)
      select @inaccessible = ltrim(str(convert(int, 0x80000000), 11))

      /* We can't 'use' a database with a version below the minimum. */
      /* SQL6.0 minimum is 406; SQL65 requires 408.  SQL70 database version is 408 now, it might change later */
      declare @mindbver smallint
      if (@@microsoftversion >= 0x07000000)
         select @mindbver = 408
      else
         select @mindbver = 406

      create table #TmpDbUserProfile (
         dbid        int NOT NULL PRIMARY KEY,
         accessperms int NOT NULL
         )

      /* Select all matching databases -- we want an entry even for inaccessible ones. */
      declare @dbid smallint, @dbidstr nvarchar(12), @dbstat int, @dbname nvarchar(258), @dbver smallint
      declare @dbbits int, @dbbitstr nvarchar(12)

      /* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a wild char */
      /* !!! but @qual2 might be '%', then = operator does not work */
      declare @temp int
      select @tempindex = charindex(N'[', @qual2)
      if (@tempindex <> 0)
         exec(N'declare hCdbs cursor global for select name, dbid, status, version from master..sysdatabases where name = N''' + @qual2 + N'''')
      else
         exec(N'declare hCdbs cursor global for select name, dbid, status, version from master..sysdatabases where name like N''' + @qual2 + N'''')

      open hCdbs

      /* Loop for each database, and if it's accessible, recursively call ourselves to add it. */
      fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      while (@@fetch_status >= 0) begin
         /* Preprocessor won't replace within quotes so have to use str(). */
         select @dbidstr = ltrim(str(convert(int, @dbid)))

         /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
         declare @single_lockedout int
         select @single_lockedout = DATABASEPROPERTY( @dbname, N'issingleuser' )
         if (@single_lockedout <> 0)
            select @single_lockedout = 0 where not exists
               (select * from master..sysprocesses p where dbid = @dbid and p.spid <> @@spid)

         /* First see if the db is accessible (not in load, recovery, offline, single-use with another user besides us, etc.) */
/*         if ((@single_lockedout <> 0) or ((@dbstat & SQLDMODBStat_Inaccessible) <> 0) or (@dbver < @mindbver)) begin   */
         if ((@single_lockedout <> 0) or
            (@dbver < @mindbver) or
            (DATABASEPROPERTY(@dbname, N'isdetached') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isshutdown') <> 0) or
            (DATABASEPROPERTY(@dbname, N'issuspect') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isoffline') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinload') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinrecovery') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isnotrecovered') <> 0) ) begin
            /* Inaccessible, but we can set dbo if we're sa or suser_id() is db owner sid. */
            exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @inaccessible + N')')
            end
         else begin
            /* Find out whether the current user has access to the database */
            select @accessbit = has_dbaccess(@dbname)
            if ( @accessbit <> 1) begin
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @invalidlogin + N')')
               end
            else begin
               /* Yes, current user does have access to this database, we are not trying to get priv at this point */
               select @dbbits = 0x01ff
               select @dbbitstr = ltrim(convert(nvarchar(12), @dbbits))
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @dbbitstr + N')')
               end
            end

         fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      end /* while FETCH_SUCCESS */
      close hCdbs
      deallocate hCdbs

      /* 1. If on all databases, then dbrole is dummy, need to get it later */
      /* 2. Do not double the ' character(s) in database name */
      /* 3. To speed up connection, accessperms column only indicate whether the login user can access the db, it does not contain */
      /*    permission info, we will retrieve the permission info through sp_MSdbuserpriv when necessary */
      /* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a wild char */
      /* !!! but @qual2 might be '%', then = operator does not work */
      if (@tempindex <> 0)
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, DatabaseProperty(o.name, 'isfulltextenabled'), o.status2 from master..sysdatabases o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name = @qual order by o.name
      else
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, DatabaseProperty(o.name, 'isfulltextenabled'), o.status2 from master..sysdatabases o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name like @qual order by o.name
      DROP TABLE #TmpDbUserProfile
      return 0
   end
go
/* End sp_MSdbuseraccess */

/*******************************************************************************/
/*******************************************************************************/
/* exec sp_MSdbuserpriv 'serv'  -- selecting the server (master db) user profile, just create db priv, if sa, 7 */
/* exec sp_MSdbuserpriv 'role'  -- selecting role membership for current db                                     */
/* exec sp_MSdbuserpriv 'ver'   -- selectversion70                                                              */
/* exec sp_MSdbuserpriv 'perm'  -- selecting user priv bit for the current db                                   */
/*******************************************************************************/
print N''
print N'Creating sp_MSdbuserpriv'
print N''
go

create proc sp_MSdbuserpriv
    @mode nvarchar(10) = N'perm'
as

/* Order of privilege evaluation is:  user granted/revoked, then group granted/revoked, then public granted/revoked */









   set nocount on
   declare @bits int, @status int, @prot int
   declare @dbrole int, @dbrolestr nvarchar(12)

   /* If 'srv', we're selecting the server (master db) user profile - currently, just create db priv. */
   if (lower(@mode) like N'serv%')
      begin
      select @bits = 0x0000
      if (user_id() = 1 or is_srvrolemember(N'sysadmin') = 1 or is_member(N'db_owner') = 1)
         begin
         /* sa has everything */
         select @bits = 0x0007
         end
      else begin
         select @prot = null select @prot = protecttype from sysprotects where action = 203 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 203 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 203 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0002

         select @prot = null select @prot = protecttype from sysprotects where action = 224 and uid = user_id() and id = object_id(N'sp_addextendedproc')
         if (@prot is null) select @prot = protecttype from sysprotects where action = 224 and uid = (select status from sysusers where uid = user_id()) and id = object_id(N'sp_addextendedproc')
         if (@prot is null) select @prot = protecttype from sysprotects where action = 224 and uid = user_id(N'public') and id = object_id(N'sp_addextendedproc')
         if (@prot = 205) select @bits = @bits | 0x0004
         end
      select @bits
      return 0
      end

   /* If 'perm', we're selecting the current database priv and role membership for the login user. */
    if (lower(@mode) like N'role%' or lower(@mode) like N'ver%' or lower(@mode) like N'perm%')
      begin
      if (user_id() = 1 or is_srvrolemember(N'sysadmin') = 1 or is_member(N'db_owner') = 1)
         begin
         /* sa/Dbo has everything. */
         select @bits = 0x01ff
         end
      else begin
         /* Not dbo so get individual privileges */
         select @bits = 0x0000, @status = status from sysusers where uid = user_id()
         select @prot = null select @prot = protecttype from sysprotects where action = 198 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 198 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 198 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0002

         select @prot = null select @prot = protecttype from sysprotects where action = 207 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 207 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 207 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0004

         select @prot = null select @prot = protecttype from sysprotects where action = 222 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 222 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 222 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0008

         select @prot = null select @prot = protecttype from sysprotects where action = 228 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 228 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 228 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0010

         select @prot = null select @prot = protecttype from sysprotects where action = 233 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 233 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 233 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0020

         select @prot = null select @prot = protecttype from sysprotects where action = 235 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 235 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 235 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0040

         select @prot = null select @prot = protecttype from sysprotects where action = 236 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 236 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 236 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0080

         select @prot = null select @prot = protecttype from sysprotects where action = 57 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 57 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 57 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0100
         end

      /* Get both Server and Database Role information */
      select @dbrole = 0x0000
      /* Server Roles */
      select @dbrole = (case when (is_srvrolemember(N'dbcreator') = 1) then @dbrole | 0x0001 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'diskadmin') = 1) then @dbrole | 0x0002 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'processadmin') = 1) then @dbrole | 0x0004 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'securityadmin') = 1) then @dbrole | 0x0008 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'serveradmin') = 1) then @dbrole | 0x0010 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'setupadmin') = 1) then @dbrole | 0x0020 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'sysadmin') = 1) then @dbrole | 0x0040 else @dbrole end),
      /* Database Roles */
             @dbrole = (case when (is_member(N'db_accessadmin') = 1) then @dbrole | 0x0080 else @dbrole end),
             @dbrole = (case when (is_member(N'db_datareader') = 1) then @dbrole | 0x0100 else @dbrole end),
             @dbrole = (case when (is_member(N'db_ddladmin') = 1) then @dbrole | 0x0200 else @dbrole end),
             @dbrole = (case when (is_member(N'db_denydatareader') = 1) then @dbrole | 0x0400 else @dbrole end),
             @dbrole = (case when (is_member(N'db_denydatawriter') = 1) then @dbrole | 0x0800 else @dbrole end),
             @dbrole = (case when (is_member(N'db_backupoperator') = 1) then @dbrole | 0x1000 else @dbrole end),
             @dbrole = (case when (is_member(N'db_owner') = 1) then @dbrole | 0x2000 else @dbrole end),
             @dbrole = (case when (is_member(N'db_securityadmin') = 1) then @dbrole | 0x4000 else @dbrole end),
             @dbrole = (case when (is_member(N'db_datawriter') = 1) then @dbrole | 0x8000 else @dbrole end)

      if (lower(@mode) like N'ver%')
         begin
         select @@version, N'login_id' = convert(int, suser_sid()), N'pagesize' = v.low, N'highbit' = v2.low, N'highbyte' = v3.low,
            N'casesens' = (case when (N'A' != N'a') then 1 else 0 end), @@spid, @@servername, is_srvrolemember(N'sysadmin'), @dbrole
            from master..spt_values v,master..spt_values v2,master..spt_values v3 where v.number=1 and v.type=N'E' and v2.number=2
            and v2.type=N'E' and v3.number=3 and v3.type=N'E'
         end
      else if (lower(@mode) like N'role%')
         begin
         select @dbrole
         end
      else if (lower(@mode) like N'perm%')
         begin
         select @bits
         end
      return 0
      end
go
/* End sp_MSdbuserpriv */


/*******************************************************************************/
print N''
print N'Creating sp_MShelpfulltextindex'
print N''
go

create proc sp_MShelpfulltextindex
   @tablename nvarchar(517)
as
   set nocount on

    create table #sphelpft
      (
         ind_name   nvarchar(128) NOT NULL,
      col1       nvarchar(128),
      col2       nvarchar(128),
      col3       nvarchar(128),
      col4       nvarchar(128),
      col5       nvarchar(128),
      col6       nvarchar(128),
      col7       nvarchar(128),
      col8       nvarchar(128),
      col9       nvarchar(128),
      col10      nvarchar(128),
      col11      nvarchar(128),
      col12      nvarchar(128),
      col13      nvarchar(128),
      col14      nvarchar(128),
      col15      nvarchar(128),
      col16      nvarchar(128)
      )

   /* all the possible full text unique indexes */
   declare @objid int
   select @objid = object_id(@tablename, N'local')
      insert #sphelpft
         select i.name,
         columnproperty( @objid, index_col(@tablename, i.indid, 1), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 2), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 3), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 4), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 5), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 6), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 7), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 8), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 9), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 10), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 11), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 12), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 13), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 14), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 15), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 16), N'AllowsNull')
         from sysindexes i where
         @objid = i.id and
         IndexProperty(@objid, i.name, N'IsUnique') = 1 and
         IndexProperty(@objid, i.name, N'UserKeyCount') = 1 and
         /* 450 byte MAX */
         exists (select * from syscolumns where id = @objid and name = Index_col(@tablename, IndexProperty(@objid, i.name, N'IndexId'), 1)
                 and length <= 450)

   /* Now we need to filter out the indexes which the associated key(s) are nullable */
   /* Each index can have up to 16 associated keys, all of them need to be non-nullalbe for the index to be qualified as a full text index */
   delete #sphelpft where col1 = 1 or col2 = 1 or col3 = 1 or col4 = 1 or col5 = 1 or col6 = 1 or col7 = 1 or col8 = 1 or
                          col9 = 1 or col10 = 1 or col11 = 1 or col12 = 1 or col13 = 1 or col14 = 1 or col15 = 1 or col16 = 1

   select ind_name from #sphelpft
   DROP TABLE #sphelpft

go
/* End sp_MShelpfulltextindex */

/*******************************************************************************/
print N''
print N'Creating sp_MShelpfulltextscript'
print N''
go

create proc sp_MShelpfulltextscript
   @tablename nvarchar(517)
as
   set nocount on

    declare @objid int
    select @objid = object_id(@tablename)
    if (@objid is null)
    begin
        RAISERROR (15001, -1, -1, @tablename)
        return 1
    end

   /* prepare the information for fulltext index scripting */
   declare @activate    int
   select @activate = OBJECTPROPERTY(@objid, N'TableFulltextCatalogId')
    if (@activate <> 0)
      begin
      declare @uniqueindex nvarchar(128)
      declare @catname     nvarchar(128)

      /* get unique index name */
      select @uniqueindex = i.name from sysindexes i where @objid = i.id and IndexProperty(@objid, i.name, N'IsFulltextKey') = 1
      /* get catalog name */
      select @catname = f.name from sysfulltextcatalogs f, sysobjects o where f.ftcatid = o.ftcatid and o.id = @objid
      if (@uniqueindex is not null and @catname is not null)
         begin
         /* is this table fulltext index activated? */
         select @activate = OBJECTPROPERTY(@objid, N'TableHasActiveFulltextIndex')
         select @uniqueindex, @catname, @activate
         end
      end

go
/* End sp_MShelpfulltextscript */


/********************* Grant privileges *********************************/
print N''
print N'Granting execute permissions on procedures'
print N''
go

/**  Mark all the SPs as system objects **/
exec sp_MS_marksystemobject sp_MShelpcolumns
go
exec sp_MS_marksystemobject sp_MShelpindex
go
exec sp_MS_marksystemobject sp_MShelptype
go
exec sp_MS_marksystemobject sp_MSdependencies
go
exec sp_MS_marksystemobject sp_MStablespace
go
exec sp_MS_marksystemobject sp_MSindexspace
go
exec sp_MS_marksystemobject sp_MStablerefs
go
exec sp_MS_marksystemobject sp_MStablekeys
go
exec sp_MS_marksystemobject sp_MStablechecks
go
exec sp_MS_marksystemobject sp_MSsettopology
go
exec sp_MS_marksystemobject sp_MSmatchkey
go
exec sp_MS_marksystemobject sp_MSforeach_worker
go
exec sp_MS_marksystemobject sp_MSforeachdb
go
exec sp_MS_marksystemobject sp_MSforeachtable
go
exec sp_MS_marksystemobject sp_MSloginmappings
go
exec sp_MS_marksystemobject sp_MSuniquename
go
exec sp_MS_marksystemobject sp_MSkilldb
go
exec sp_MS_marksystemobject sp_MSobjectprivs
go
exec sp_MS_marksystemobject sp_MSSQLDMO70_version
go
exec sp_MS_marksystemobject sp_MSSQLOLE65_version
go
exec sp_MS_marksystemobject sp_MSSQLOLE_version
go
exec sp_MS_marksystemobject sp_MSscriptdatabase
go
exec sp_MS_marksystemobject sp_MSscriptdb_worker
go
exec sp_MS_marksystemobject sp_MSdbuseraccess
go
exec sp_MS_marksystemobject sp_MSdbuserpriv
go
exec sp_MS_marksystemobject sp_MShelpfulltextindex
go
exec sp_MS_marksystemobject sp_MShelpfulltextscript
go

grant execute on sp_MShelpcolumns to public
grant execute on sp_MShelpindex to public
grant execute on sp_MShelptype to public
grant execute on sp_MSdependencies to public
grant execute on sp_MStablespace to public
grant execute on sp_MSindexspace to public
grant execute on sp_MSuniquename to public
grant execute on sp_MSkilldb to public
grant execute on sp_MSobjectprivs to public
grant execute on sp_MSloginmappings to public
grant execute on sp_MStablekeys to public
grant execute on sp_MStablechecks to public
grant execute on sp_MStablerefs to public
grant execute on sp_MSsettopology to public
grant execute on sp_MSmatchkey to public
grant execute on sp_MSforeachdb to public
grant execute on sp_MSforeachtable to public
grant execute on sp_MSforeach_worker to public
grant execute on sp_MSSQLOLE_version to public
grant execute on sp_MSSQLOLE65_version to public
grant execute on sp_MSSQLDMO70_version to public
grant execute on sp_MSscriptdatabase to public
grant execute on sp_MSscriptdb_worker to public
grant execute on sp_MSdbuseraccess to public
grant execute on sp_MSdbuserpriv to public
grant execute on sp_MShelpfulltextindex to public
grant execute on sp_MShelpfulltextscript to public
go

















/********************* Delete existing objects *********************************/
print ''
print 'Deleting existing objects...'
print ''
go

if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSfilterclause')
    drop procedure sp_MSfilterclause
go

/********************* Create new objects *********************************/

print ''
print 'Creating sp_MSfilterclause'
print ''
go

create procedure sp_MSfilterclause
    @publication nvarchar(258), @article nvarchar(258)
as
    /* Return a text column as multiple readtexts of maxcol length */
    declare @pubid int, @artid int
    select @pubid = pubid from syspublications where name = @publication
    if (@pubid is null) begin
        RAISERROR (15001, 11, -1, @publication)
        return 1
    end
    select @artid = artid from sysarticles where name = @article and pubid = @pubid
    if (@artid is null) begin
        RAISERROR (15001, 11, -1, @article)
        return 1
    end

    declare @val varbinary(16), @len int, @ii int, @chunk int
    -- filter clause is in unicode, the length is a half of the number of bytes.
    select @val = textptr(filter_clause), @len = datalength(filter_clause)/2 from sysarticles where artid = @artid and pubid = @pubid
    select @ii = 0, @chunk = 255

    /* Get all the rows of an maxcol size */
    while @len > @chunk begin
        readtext sysarticles.filter_clause @val @ii @chunk
        select @ii = @ii + @chunk, @len = @len - @chunk
    end

    /* Get the last chunk */
    if (@len > 0)
        readtext sysarticles.filter_clause @val @ii @len
    return 0
go
/* End sp_MSfilterclause */


/********************* Grant privileges *********************************/
print ''
print 'Granting execute permissions on procedures'
print ''
go

exec sp_MS_marksystemobject sp_MSfilterclause
go

grant execute on sp_MSfilterclause to public














/********************* Delete existing objects *********************************/
print N''
print N'Deleting existing objects...'
print N''
go

if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = 'sp_MSgetalertinfo')
    drop procedure sp_MSgetalertinfo
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = 'sp_MSsetalertinfo')
    drop procedure sp_MSsetalertinfo
go

/********************* Create new objects *********************************/

print N''
print N'Creating sp_MSgetalertinfo'
print N''
go
create procedure sp_MSgetalertinfo
    @includeaddresses bit = 0
as
    /* Return all alert info at one go, for performance reasons. */
    declare @FailSafeOperator nvarchar(255)
    declare @NotificationMethod int
    declare @ForwardingServer nvarchar(255)
    declare @ForwardingSeverity int
    declare @ForwardAlways int
    declare @PagerToTemplate nvarchar(255)
    declare @PagerCCTemplate nvarchar(255)
    declare @PagerSubjectTemplate nvarchar(255)
    declare @PagerSendSubjectOnly int
    declare @FailSafeEmailAddress nvarchar(255)
    declare @FailSafePagerAddress nvarchar(255)
    declare @FailSafeNetSendAddress nvarchar(255)

    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator', @param = @FailSafeOperator OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertNotificationMethod', @param = @NotificationMethod OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingServer', @param = @ForwardingServer OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingSeverity', @param = @ForwardingSeverity OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardAlways', @param = @ForwardAlways OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerToTemplate', @param = @PagerToTemplate OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerCCTemplate', @param = @PagerCCTemplate OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSubjectTemplate', @param = @PagerSubjectTemplate OUT, @no_output = N'no_output'
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSendSubjectOnly', @param = @PagerSendSubjectOnly OUT, @no_output = N'no_output'

    if (@includeaddresses <> 0) begin
        exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeEmailAddress', @param = @FailSafeEmailAddress OUT, @no_output = N'no_output'
        exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafePagerAddress', @param = @FailSafePagerAddress OUT, @no_output = N'no_output'
        exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeNetSendAddress', @param = @FailSafeNetSendAddress OUT, @no_output = N'no_output'
    end

    select
        AlertFailSafeOperator = @FailSafeOperator,
        AlertNotificationMethod = @NotificationMethod,
        AlertForwardingServer = @ForwardingServer,
        AlertForwardingSeverity = @ForwardingSeverity,
        AlertPagerToTemplate = @PagerToTemplate,
        AlertPagerCCTemplate = @PagerCCTemplate,
        AlertPagerSubjectTemplate = @PagerSubjectTemplate,
        AlertPagerSendSubjectOnly = @PagerSendSubjectOnly,
        AlertForwardAlways = ISNULL(@ForwardAlways, 0)

    if (@includeaddresses <> 0)
        select
            AlertFailSafeEmailAddress = @FailSafeEmailAddress,
            AlertFailSafePagerAddress = @FailSafePagerAddress,
            AlertFailSafeNetSendAddress = @FailSafeNetSendAddress
go
/* End sp_MSgetalertinfo */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSsetalertinfo'
print N''
go
create procedure sp_MSsetalertinfo
    @failsafeoperator nvarchar(255) = null,
    @notificationmethod int = null,
    @forwardingserver nvarchar(255) = null,
    @forwardingseverity int = null,
    @pagertotemplate nvarchar(255) = null,
    @pagercctemplate nvarchar(255) = null,
    @pagersubjecttemplate nvarchar(255) = null,
    @pagersendsubjectonly int = null,
    @failsafeemailaddress nvarchar(255) = null,
    @failsafepageraddress nvarchar(255) = null,
    @failsafenetsendaddress nvarchar(255) = null,
    @forwardalways int = null -- 0 = forward only unhandled events, 1 = always forward events (both subject to @forwardingseverity)
as
    /* Set all alert info at one go, for performance reasons.  Translate values if needed. */
    if (@pagersendsubjectonly is not null and @pagersendsubjectonly <> 0)
        select @pagersendsubjectonly = 1

    if (@failsafeoperator is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator', N'REG_SZ', @failsafeoperator
    if (@notificationmethod is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertNotificationMethod', N'REG_DWORD', @notificationmethod
    if (@forwardingserver is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingServer', N'REG_SZ', @forwardingserver
    if (@forwardingseverity is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingSeverity', N'REG_DWORD', @forwardingseverity
    if (@pagertotemplate is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerToTemplate', N'REG_SZ', @pagertotemplate
    if (@pagercctemplate is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerCCTemplate', N'REG_SZ', @pagercctemplate
    if (@pagersubjecttemplate is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSubjectTemplate', N'REG_SZ', @pagersubjecttemplate
    if (@pagersendsubjectonly is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSendSubjectOnly', N'REG_DWORD', @pagersendsubjectonly
    if (@failsafeemailaddress is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeEmailAddress', N'REG_SZ', @failsafeemailaddress
    if (@failsafepageraddress is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafePagerAddress', N'REG_SZ', @failsafepageraddress
    if (@failsafenetsendaddress is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeNetSendAddress', N'REG_SZ', @failsafenetsendaddress
    if (@forwardalways is not null)
        exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardAlways', N'REG_DWORD', @forwardalways
go
/* End sp_MSgetalertinfo */

/********************* Grant privileges *********************************/
print N''
print N'Granting execute permissions on procedures'
print N''
go

exec sp_MS_marksystemobject sp_MSgetalertinfo
go
exec sp_MS_marksystemobject sp_MSsetalertinfo
go

grant execute on sp_MSgetalertinfo to public
grant execute on sp_MSsetalertinfo to public


/********************** Verify object creation and update category bit for objects *********************************/





print ''
print 'Updating category for objects created by SQLDMO70.sql.'
print ''
go

sp_configure 'allow updates', 1
go
reconfigure with override
go

/* if (@@microsoftversion >= SQL70_MINVERSION) begin                                      */
/*     exec sp_MS_upd_sysobj_category 2                                                    */
/* end else begin                                                                         */
/*     RAISERROR 55555 'You need a released version of SQL 7.0 to run this SQLDMO script'  */
/* end                                                                                    */
if (@@microsoftversion < 0x07000000) begin
    RAISERROR 55555 'You need a released version of SQL 7.0 to run this SQLDMO script'
end
go

sp_configure 'allow updates', 0
go
reconfigure with override
go

if (object_id('sp_MSSQLDMO70_version') is not null) begin
    print ''
    print ''
    print ' Successful installation.'
    exec sp_MSSQLDMO70_version
end

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print ''
print 'Dumping transaction log...'
print ''
go
dump tran master with no_log
go
checkpoint
go
/************* END DUMP THE TRANSACTION LOG **********************************/